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.

回答1:

PostgreSQL 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 question about custom variables:
Is there a way to define a named constant in a PostgreSQL query?