10

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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
ishk
  • 1,873
  • 5
  • 19
  • 22

1 Answers1

27

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:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • 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? – Reinsbrain May 14 '18 at 19:31
  • 3
    @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://stackoverflow.com/a/9067777/939860, https://stackoverflow.com/a/30179814/939860 – Erwin Brandstetter Sep 20 '18 at 21:07