sqldatabasepostgresqldynamic-variablesuser-variables

User-defined variables in PostgreSQL

I have the following MySQL script which I want to implement in PostgreSQL.

SET @statement = search_address_query;
      PREPARE dynquery FROM @statement;
      EXECUTE dynquery;
      DEALLOCATE PREPARE dynquery;

How can I define user-defined variable @statement using PostgreSQL.

Solution

  • 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: