12

In Microsoft SQL Server, to test something like this in the query window:

select * from Users where LastName = @lastname

I can add this before the command:

declare @lastname varchar(16)
set @lastname = 'Troy'

But in PostgreSQL, I cannot find a similar way to do so. It seems the only thing I can do is to replace the parameter name directly with its value. It gets hard when the ad-hoc query gets complicated and the same parameter gets used several times. Is there a way?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Carol
  • 363
  • 5
  • 16
  • BTW: "Postgres query window"? You mean a pgAdmin query window? – Erwin Brandstetter May 12 '15 at 01:18
  • 2
    I too was used to the workflow from MSSQL of copying a parameterized query into query runner, setting declarations above it, and hitting run. That way I don't mistakenly change the query semantics in the process of modifying it for testing. It's pretty disappointing that there is no way to do this in postgres. One must always change a parameterized query (at the very least, variable names or inserting casting) to test it and risk an invalid test result. – Kasey Speakman Jan 24 '17 at 22:48
  • Workflow also exists in MySQL using `SET @var = 123`. http://stackoverflow.com/questions/11754781/how-to-declare-a-variable-in-mysql. I knew I had used it before MSSQL. – Kasey Speakman Jan 25 '17 at 00:12

3 Answers3

11

Various options.

Provide parameters in a CTE to have "variables" in pure SQL:

WITH var(lastname) AS (SELECT 'Troy'::varchar(16))
SELECT *
FROM   users, var v
WHERE  lastname = v.lastname;

This works for any query.
Since the CTE var holds a single row it is safe to append it with a CROSS JOIN at the end of the FROM clause - actually the short form with appending it after a comma may be best because explicit join syntax binds before commas. The additional table alias v is optional to further shorten the syntax.

OR cheaper without CTE. BTW, why varchar(16)? Just use text:

SELECT *
FROM   users
JOIN  (SELECT 'Troy'::text) var(lastname) USING (lastname)
WHERE  lastname = var.lastname;

Or use a temporary table to play a similar role for all queries within the same session. Temp tables die with the end of the session.

CREATE TEMP TABLE var AS
SELECT text 'Troy' AS lastname;

ANALYZE var;  -- temp tables are not covered by autovacuum

SELECT * FROM users JOIN var USING (lastname);

Or you can use DO statements like @Houari supplied or like demonstrated here:

Note that you cannot return values from DO statements. (You can use RAISE ... though.) And you cannot use SELECT without target in plpgsql - the default procedural language in a DO statement. Replace SELECT with PERFORM to throw away results.

Or you can use customized options, which you can set in postgresql.conf to be visible globally.

Or set in your session to be visible for the duration of the session and only in the same session:

SET my.lastname = 'Troy';

The variable name must include a dot. You are limited to text as data type this way, but any data type can be represented as text ...

You can use current_setting('my.lastname') as value expression. Cast if you need. For example: current_setting('my.json_var')::json ...

Or use SET LOCAL for the effect to only last for the current transaction. See:

Or you can use tiny IMMUTABLE functions as global persisted variables that only privileged users can manipulate. See:

Or when working with psql as client, use the \set or \gset meta-commands and variable substitution.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
3

Perhaps using the DO instruction to simulate a function. In that way you can declare variables and use them to execute your query!

http://www.postgresql.org/docs/9.4/static/sql-do.html

Houari
  • 5,326
  • 3
  • 31
  • 54
  • I tried, however it always gets a sql error: ERROR: query has no destination for result data SQL state: 42601 Hint: If you want to discard the results of a SELECT, use PERFORM instead. Context: PL/pgSQL function inline_code_block line 5 at SQL statement DO $$ DECLARE startDate Timestamp; BEGIN startDate := '2014-4-1'; select pid from grouping_horizontal where dt = startDate; END $$; – Carol May 12 '15 at 00:45
  • @Carol: The hint says it all: Use the key word `PERFORM` instead of `SELECT`. You cannot `SELECT` without target in a `DO` statement and you cannot return rows ... – Erwin Brandstetter May 12 '15 at 01:09
0

I config postgres to log all the commands, and copy the command from the log file, so all the parameters are already replaced with the value, and test the command in the query window.

May not be the best approach, but it is easy and works for me

Carol
  • 363
  • 5
  • 16