Cómo declarar una variable en una consulta PostgreSQL

¿Cómo declaro una variable para su uso en una consulta PostgreSQL 8.3?

En MS SQL Server puedo hacer esto: 

DECLARE @myvar INT
SET @myvar = 5

SELECT *
FROM somewhere
WHERE something = @myvar

¿Cómo hago lo mismo en PostgreSQL? De acuerdo con la documentación, las variables se declaran simplemente como " name type;", pero esto me da un error de sintaxis: 

myvar INTEGER;

Podría alguien darme un ejemplo de la sintaxis correcta?

Author: ypercubeᵀᴹ, 2009-09-29
Source

8 answers

No existe tal característica en PostgreSQL. Solo puede hacerlo en pl / PgSQL (u otro pl/*), pero no en SQL simple.

Una excepción es la consulta WITH () que puede funcionar como una variable, o incluso tuple de variables. Permite devolver una tabla de valores temporales. 

WITH master_user AS (
    SELECT
      login,
      registration_date
    FROM users
    WHERE ...
)

SELECT *
FROM users
WHERE master_login = (SELECT login
                      FROM master_user)
      AND (SELECT registration_date
           FROM master_user) > ...;
 58
Author: J.Wincewicz,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2018-08-09 12:39:18

Logré el mismo objetivo usando un WITH clause, no es ni de lejos tan elegante pero puede hacer lo mismo. Aunque para este ejemplo es realmente exagerado. Tampoco recomiendo particularmente esto.

WITH myconstants (var1, var2) as (
   values (5, 'foo')
)
SELECT *
FROM somewhere, myconstants
WHERE something = var1
   OR something_else = var2;
 142
Author: fei0x,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2018-02-21 18:51:29

También puedes probar esto en PLPGSQL: 

DO $$
DECLARE myvar integer;
BEGIN
    SELECT 5 INTO myvar;

    DROP TABLE IF EXISTS tmp_table;
    CREATE TABLE tmp_table AS
    SELECT * FROM yourtable WHERE   id = myvar;
END $$;

SELECT * FROM tmp_table;

Lo anterior requiere Postgres 9.0 o posterior.

 52
Author: Dario Barrionuevo,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2016-11-15 15:38:31

Depende de su cliente.

Sin embargo, si estás usando el cliente psql, entonces puedes usar lo siguiente: 

my_db=> \set myvar 5
my_db=> SELECT :myvar  + 1 AS my_var_plus_1;
 my_var_plus_1 
---------------
             6
 34
Author: Shahriar Aghajani,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-11-29 02:44:58

Configuración dinámica

Puede "abusar" de la configuración dinámica para esto:

-- choose some prefix that is unlikey to be used by postgres
set session my.vars.id = '1';

select *
from person 
where id = current_setting('my.vars.id')::int;

Los ajustes de configuración son siempre valores varchar, por lo que debe enviarlos al tipo de datos correcto cuando los use. Esto funciona con cualquier cliente SQL mientras que \set solo funciona en psql

Lo anterior requiere Postgres 9.2 o posterior.

Para versiones anteriores, la variable tenía que ser declarada en postgresql.conf antes de ser utilizada, por lo que limitaba un poco su usabilidad. En realidad no la variable completamente, pero la "clase" de configuración que es esencialmente el prefijo. Pero una vez definido el prefijo, cualquier variable podría ser utilizada sin cambiar postgresql.conf

 33
Author: a_horse_with_no_name,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2016-11-11 09:50:48

Usando una tabla Temporal fuera de pl / PgSQL

Fuera del uso de pl/pgsql u otro lenguaje pl/* como se sugiere, esta es la única otra posibilidad que se me ocurrió.

begin;
select 5::int as var into temp table myvar;
select *
  from somewhere s, myvar v
 where s.something = v.var;
commit;
 18
Author: Evan Carroll,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2016-11-01 18:16:19

Quiero proponer una mejora a la respuesta de @DarioBarrionuevo, para hacerlo más simple aprovechando las tablas temporales.

DO $$
    DECLARE myvar integer = 5;
BEGIN
    CREATE TEMP TABLE tmp_table ON COMMIT DROP AS
        -- put here your query with variables:
        SELECT * 
        FROM yourtable
        WHERE id = myvar;
END $$;

SELECT * FROM tmp_table;
 5
Author: bluish,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-05-23 12:26:34

Aquí hay un ejemplo usando PREPARE declaraciones. Todavía no puedes usar ?, pero puedes usar $n notación: 

PREPARE foo(integer) AS
    SELECT  *
    FROM    somewhere
    WHERE   something = $1;
EXECUTE foo(5);
DEALLOCATE foo;
 2
Author: Martin Zinovsky,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2018-01-30 16:54:03