User-defined variables in PostgreSQL

Question

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.

Characteristic

Can you simply EXECUTE search_address_query;? – Chessa

Answer 1

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:

• Is there a way to define a named constant in a PostgreSQL query?

Ranite

how long does a custom setting persist? is it only for a session? is it globally accessible by other sessions? does it persist after service restart? – Ambivalence

@Reinsbrain: Depends how you set it. Settings in postgresql.conf are preset for every connection. The effect of SET is for the duration of the current session and only visible in the same session. SET LOCAL only for the transaction. There are more ways to set options: https://mcmap.net/q/28658/-how-does-the-search_path-influence-identifier-resolution-and-the-quot-current-schema-quot, https://mcmap.net/q/28802/-how-to-test-my-ad-hoc-sql-with-parameters-in-postgres-query-window – Ranite

Answer 2

For example, you can set the custom options my.num and my.first.name as variables as shown below. *My answer explains how to declare user-defined variables in detail:

SET my.num = 2;
SET my.first.name = 'John';

Or, you can set the custom options with set_config() as shown below:

SELECT set_config('my.num', '2', false);
SELECT set_config('my.first.name', 'John', false);

Then, you must use current_setting() to get the values of the custom options my.num and my.first.name as shown below:

postgres=# SELECT current_setting('my.num');
 current_setting
-----------------
 2
(1 row)

postgres=# SELECT current_setting('my.first.name');
 current_setting
-----------------
 John
(1 row)

Next for example, you can use \set to set the number 2 to num as shown below:

postgres=# \set num 2
postgres=# SELECT :num;
 ?column?
----------
        2
(1 row)

And, you can use \set to set the text John Smith with '' to name as shown below:

postgres=# \set name 'John Smith'
postgres=# SELECT :'name';
  ?column?
------------
 John Smith
(1 row)

Bergsonism