Postgresql – Numeric variables in psql

postgresqlpsql

I am running a number of queries in psql using for a time interval where a part of the query goes like

 where g2.datetime between g1.datetime+'19 minutes' and g1.datetime+'21 minutes'

i.e. I want an interval around 20 minutes after g1.datetime

If I want to do this several times, both to make it easier and less error prone (I am also refering to 20 minutes a couple of times in the query). I intended to do something like:

\set timelag 20
\set before :timelag-1
\set after :timelag+1

if I do a 

select :timelag, :before, :after;

this seems to work nicely, but obiously, I am just concatenating strings (so the query is really 'select 20, 20-1, 20+1'), because if I do try make what I need:

\set beforemin '\'' :before ' minutes\''

it turns out that what I have is '20-1 minutes' – that does not work as intended in the query.

Is there any possibility to make psql variables work as numbers or is that beyond the scope of those so I will have to define the query in some other language or as a function?

Best Answer

Not sure of the question, but psql doesn't have variables at all. It's just a macro language.

\set p 50
\set z 50::numeric(20,5)

SELECT pg_typeof(:p), pg_typeof(:z);
 pg_typeof | pg_typeof 
-----------+-----------
 integer   | numeric
(1 row)

\set after '''1 day''::interval'
\set before '''1 day''::interval'
SELECT 1 WHERE now() BETWEEN now() - :before AND now() + :after;
Related Question