CODEWITHSUNDEEP
postgresqlplpgsqldynamic-sql
user3206440
user3206440

Reputation: 5049

prepared statement - using parameter to specify table name

using pgadmin4, postgres 9.6 on windows 10

I'm trying to use parameter to specify table name in a prepared statement as in the code below. However I do get a syntax error as below. Note that I'm able to use the parameters with a where condition et al.

Query

prepare mySelect(text) as 
    select * 
        from $1
        limit 100;

execute mySelect('some_table');

pgAdmin message

ERROR:  syntax error at or near "$1"
LINE 3:      from $1
                  ^
SQL state: 42601
Character: 50

Upvotes: 3

Views: 2507

Answers (1)

Pavel Stehule
Pavel Stehule

Reputation: 45795

It is not possible. The prepare statement is persistent execution plan - and execution plan contains pined source of data - so tables, column names cannot be mutable there.

When you change table, columns, then you change the semantic of query - you will got different execution plan and then this behave is not possible in prepared statements. The main use case of prepared statements is reusing of execution plans - plan once, execute more. But there are some principal limits - only some parameters can be changed.

Upvotes: 4

Related Questions