How to save temporary session variables in PostgreSQL

user_11942492 0
Viewed 923

Say I have a simple SQL statement like this:

SELECT * from birthday WHERE date < now() + INTERVAL '1 day' AND date > now() - INTERVAL '1 day';

but the equations for the "From" and "To" variables would be something more sophisticated than that. And I will be reusing the results of these equations in multiple queries. So, can I store them temporarily as we do in any programming language? I imagine something like:

$from := now() - INTERVAL '1 day';
$to:= now() + INTERVAL '1 day';
SELECT * from birthday WHERE date < $from AND date > $to;

I tried using SELECT INTO as suggested in this question but that's not what I want because it creates a whole database table just to save the variable, which also causes an error when reusing it in a later session even when using the TEMP parameter. It says "relationship already exists"! I also tried some dollar sign $ syntax and some colon+equal := syntax and none works

postgresql sql
1 Answers

SQL is not any programming language. If you want to store the values so you can re-use them in one query, then you can use a CTE:

WITH params as (
      SELECT now() - INTERVAL '1 day' as _from,
             now() + INTERVAL '1 day' as _to
     )
SELECT * 
FROM params CROSS JOIN
     birthday b
WHERE date < params._to AND date > params._from;

If you want to repeat this across multiple queries, then I would recommend a temporary table:

CREATE TEMPORARY TABLE params AS
      SELECT now() - INTERVAL '1 day' as _from,
             now() + INTERVAL '1 day' as _to;

SELECT * 
FROM params CROSS JOIN
     birthday b
WHERE date < params._to AND date > params._from;

You can also encapsulate the code in a procedure/function. Or use some sort of scripting language or language such as Python.

user_11440350
Related