Re: Table as argument in postgres function

po 20. 5. 2019 v 7:56 odesílatel Corey Huinker <corey(dot)huinker(at)gmail(dot)com>
napsal:

>
>> You can pass table name as text or table object id as regclass type.
>>
>> inside procedure you should to use dynamic sql - execute statement.
>> Generally you cannot to use a variable as table or column name ever.
>>
>> Dynamic SQL is other mechanism - attention on SQL injection.
>>
>
> On this note, Snowflake has the ability to to parameterize object names
> (see:
> https://docs.snowflake.net/manuals/sql-reference/identifier-literal.html )
>
> So you can do things like
> SELECT col_a, col_b FROM identifier('a_table_name')
> or as a bind variable
> SELECT col_a, col_b FROM identifier($1)
>
> Which is their way of avoiding SQL injection attacks in *some* circumstances.
> Their implementation of it is a bit uneven, but it has proven useful for my
> work.
>
> I can see where this obviously would prevent the planning of a prepared
> statement when a table name is a parameter, but the request comes up often
> enough, and the benefits to avoiding SQL injection attacks are significant
> enough that maybe we should try to enable it for one-off. I don't
> necessarily think we need an identifier(string) function, a
> 'schema.table'::regclass would be more our style.
>
> Is there anything preventing us from having the planner resolve object
> names from strings?
>

The basic problem is fact so when you use PREPARE, EXECUTE protocol, you
has not parameters in planning time.

Regards

Pavel