User defined variables in PostgreSQL

by

Postgres does not normally use variables in plain SQL. But you can do that, too:

SET foo.test="SELECT bar FROM baz";

SELECT current_setting('foo.test');

Read about Customized Options in the manual.

In PostgreSQL 9.1 or earlier you needed to declare custom_variable_classes before you could use that.

However, You cannot EXECUTE dynamic SQL without a PL (procedural language). You would use a DO command for executing ad-hoc statements (but you cannot return data from it). Or use CREATE FUNCTION to create a function that executes dynamic SQL (and can return data in any fashion imaginable).

Be sure to safeguard against SQL injection when using dynamic SQL.

Related:

More Related Contents:

  1. INSERT with dynamic table name in trigger function
  2. Truncating all tables in a Postgres database
  3. Define table and column names as arguments in a plpgsql function?
  4. How do you use variables in a simple PostgreSQL script?
  5. dynamic sql query in postgres
  6. Execute a dynamic crosstab query
  7. Can I make a plpgsql function return an integer without using a variable?
  8. Dynamic SQL (EXECUTE) as condition for IF statement
  9. Test for null in function with varying parameters
  10. Return SETOF rows from PostgreSQL function
  11. Error when setting n_distinct using a plpgsql variable
  12. Dynamically generate columns for crosstab in PostgreSQL
  13. Use variable set by psql meta-command inside of DO block
  14. Function to loop through and select data from multiple tables
  15. Loop on tables with PL/pgSQL in Postgres 9.0+
  16. Format specifier for integer variables in format() for EXECUTE?
  17. How to make “case-insensitive” query in Postgresql?
  18. Rownum in postgresql
  19. Cannot create a database table named ‘user’ in PostgreSQL
  20. Is there a timeout for idle PostgreSQL connections?
  21. How to make connection to Postgres via Node.js
  22. gem install pg –with-pg-config works, bundle fails
  23. Unable to connect PostgreSQL to remote database using pgAdmin
  24. Docker wait for postgresql to be running
  25. When do Postgres column or table names need quotes and when don’t they?
  26. How can I get a list of all functions stored in the database of a particular schema in PostgreSQL?
  27. Composite PRIMARY KEY enforces NOT NULL constraints on involved columns
  28. Why unsigned integer is not available in PostgreSQL?
  29. now() default values are all showing same timestamp
  30. PostgreSQL – dynamic value as table name [duplicate]

Leave a Comment