Postgresql dynamic query with named parameters inside

Question

Is there a possibility to write dynamic SQL, where I can use parameter names inside the code?

The following syntax I know, makes long code completely unclear and hard to figure out the sense without digging and mapping the parameters.

format ($$ select * from %1$s $$ , tableName_1)

I would like to somehow have it like

select * from tableName_1

Its is `I%` not `1%`. And it is not possible. In `plpgsql` there is the ability to do dynamic SQL, but it is not that different from the above. For information on that see [Dynamic SQL](https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN). — Adrian Klaver, Mar 29 '22 at 15:32
Of course the syntax I mentioned is possible... I'm already using it and it works. The numbers represents parameters in their order, and can be used like this: format ($$ select * from %1$s where %3$s is true and %2$s > 1 $$ , paremeter1, parameter2, parameter3) — sh4rkyy, Mar 29 '22 at 16:32
Yes it is possible but for identifiers you should be using `I` to get proper quoting. — Adrian Klaver, Mar 29 '22 at 16:41

score 0 · Answer 1 · answered Mar 29 '22 at 23:14

0

Sample1:

using format function. By using format you can write all parameters under same name. And values will be set to params orderly.

execute format('select * from %s.%s' , p_schema, p_table);

--Result:
select * from public.mytable

Sample2:

execute 'select * from ?.?' using p_schema, p_table;

Sample3:

execute 'select * from $1.$2' using p_schema, p_table;

answered Mar 29 '22 at 23:14

Ramin Faracov

But I want just the opposite, so having the code like select * from p_schema.p_table, so if my function is really long I can see on the first look which parameters are on certain places – sh4rkyy Mar 30 '22 at 12:10

1 Answers1