dynamic sql query in postgres

by

EXECUTE ... USING only works in PL/PgSQL – ie within functions or DO blocks written in the PL/PgSQL language. It does not work in plain SQL; the EXECUTE in plain SQL is completely different, for executing prepared statements. You cannot use dynamic SQL directly in PostgreSQL’s SQL dialect.

Compare:

See the 2nd last par in my prior answer.

In addition to not running except in PL/PgSQL your SQL statement is wrong, it won’t do what you expect. If (select id from ids where condition = some_condition) returns say 42, the statement would fail if id is an integer. If it’s cast to text you’d get:

EXECUTE format('SELECT * from result_%s_table', quote_ident('42'));
EXECUTE format('SELECT * from result_%s_table', '"42"');
EXECUTE 'SELECT * from result_"42"_table';

That’s invalid. You actually want result_42_table or "result_42_table". You’d have to write something more like:

EXECUTE format('SELECT * from %s', quote_ident('result_'||(select id from ids where condition = some_condition)||'_table'))

… if you must use quote_ident.

More Related Contents:

  1. INSERT with dynamic table name in trigger function
  2. Truncating all tables in a Postgres database
  3. Execute a dynamic crosstab query
  4. Dynamic SQL (EXECUTE) as condition for IF statement
  5. User defined variables in PostgreSQL
  6. Test for null in function with varying parameters
  7. Return SETOF rows from PostgreSQL function
  8. Error when setting n_distinct using a plpgsql variable
  9. Dynamically generate columns for crosstab in PostgreSQL
  10. Use variable set by psql meta-command inside of DO block
  11. Function to loop through and select data from multiple tables
  12. Format specifier for integer variables in format() for EXECUTE?
  13. How to reset postgres’ primary key sequence when it falls out of sync?
  14. Refactor a PL/pgSQL function to return the output of various SELECT queries
  15. Calculating and saving space in PostgreSQL
  16. Passing user id to PostgreSQL triggers
  17. Do nullable columns occupy additional space in PostgreSQL?
  18. how to emulate “insert ignore” and “on duplicate key update” (sql merge) with postgresql?
  19. How do I efficiently select the previous non-null value?
  20. Postgres password authentication fails
  21. Generate series of dates – using date type as input
  22. Copy a few of the columns of a csv file into a table
  23. Postgres query to check a string is a number
  24. ERROR: permission denied for relation tablename on Postgres while trying a SELECT as a readonly user
  25. How do you create a random string that’s suitable for a session ID in PostgreSQL?
  26. PL/pgSQL functions: How to return a normal table with multiple columns using an execute statement
  27. Update timestamp when row is updated in PostgreSQL
  28. Multicolumn index on 3 fields with heterogenous data types
  29. Select from a table variable
  30. now() default values are all showing same timestamp

Leave a Comment