CODEWITHSUNDEEP
sqldatabasepostgresqldynamic-variablesuser-variables
ishk
ishk

Reputation: 1993

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.

Upvotes: 10

Views: 34408

Answers (2)

Super Kai - Kazuya Ito
Super Kai - Kazuya Ito

Reputation: 1

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)

Upvotes: 0

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656331

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:

Upvotes: 29

Related Questions