How to save temporary session variables in PostgreSQL

Question

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

Gordon Linoff · Accepted Answer · 2020-12-19 13:50:04Z

6

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.

answered Dec 19, 2020 at 13:50

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Marcos Pereira Over a year ago

For those wondering what CTE stands for: WITH Queries are referred to as "Common Table Expressions" in the Postgres docs postgresql.org/docs/current/queries-with.html

Collectives™ on Stack Overflow

How to save temporary session variables in PostgreSQL

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related