Jak zadeklarować zmienną do użycia w zapytaniu PostgreSQL 8.3?

W MS SQL Server mogę to zrobić:

DECLARE @myvar INT
SET @myvar = 5

SELECT *
FROM somewhere
WHERE something = @myvar

Jak zrobić to samo w PostgreSQL? Zgodnie z dokumentacją zmienne są zadeklarowane po prostu jako „typ nazwy”, ale daje mi to błąd składniowy:

myvar INTEGER;

Czy ktoś mógłby mi podać przykład poprawnej składni?

W PostgreSQL nie ma takiej funkcji. Możesz to zrobić tylko w pl / PgSQL (lub innym pl / *), ale nie w zwykłym SQL.

Wyjątkiem jest WITH ()zapytanie, które może działać jako zmienna, a nawet tuplezmienne. Pozwala zwrócić tabelę wartości tymczasowych.

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) > ...;

Osiągnąłem ten sam cel, używając WITHklauzuli , nie jest to tak eleganckie, ale może zrobić to samo. Chociaż w tym przykładzie jest to naprawdę przesada. Też nie polecam tego szczególnie.

WITH myconstants (var1, var2) as (
   values (5, 'foo')
)
SELECT *
FROM somewhere, myconstants
WHERE something = var1
   OR something_else = var2;

Możesz także spróbować tego w 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;

Powyższe wymaga Postgres 9.0 lub nowszego.

Ustawienia dynamicznej konfiguracji

możesz „nadużyć” ustawień dynamicznej konfiguracji w tym celu:

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

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

Ustawienia konfiguracji są zawsze wartościami varchar, więc podczas ich używania musisz rzutować je na poprawny typ danych. Działa to z każdym klientem SQL, podczas gdy \setdziała tylko wpsql

Powyższe wymaga Postgres 9.2 lub nowszego.

W poprzednich wersjach zmienną trzeba było zadeklarować w postgresql.conf przed użyciem, więc ograniczyła nieco jej użyteczność. W rzeczywistości nie jest to zmienna całkowicie, ale konfiguracyjna „klasa”, która jest zasadniczo przedrostkiem. Ale po zdefiniowaniu prefiksu można użyć dowolnej zmiennej bez zmianypostgresql.conf

To zależy od twojego klienta.

Jeśli jednak używasz klienta psql , możesz użyć następujących opcji:

my_db=> \set myvar 5
my_db=> SELECT :myvar  + 1 AS my_var_plus_1;
 my_var_plus_1 
---------------
             6

Jeśli używasz zmiennych tekstowych, musisz zacytować.

\set myvar 'sometextvalue'
select * from sometable where name = :'myvar';

Używanie tabeli temperatur poza pl / PgSQL

Poza użyciem sugerowanego języka pl / pgsql lub innego języka pl / *, jest to jedyna inna możliwość, o której mogłem pomyśleć.

begin;
select 5::int as var into temp table myvar;
select *
  from somewhere s, myvar v
 where s.something = v.var;
commit;

Chcę zaproponować ulepszenie odpowiedzi @ DarioBarrionuevo , aby ułatwić korzystanie z tabel tymczasowych.

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;

To rozwiązanie jest oparte na rozwiązaniu zaproponowanym przez fei0x, ale ma tę zaletę, że nie ma potrzeby dołączania do listy wartości stałych w zapytaniu, a stałe można łatwo wymienić na początku zapytania. Działa również w zapytaniach rekurencyjnych.

Zasadniczo każda stała jest tabelą pojedynczej wartości zadeklarowaną w klauzuli WITH, którą można następnie wywołać w dowolnym miejscu w pozostałej części zapytania.

• Podstawowy przykład z dwiema stałymi:

WITH
    constant_1_str AS (VALUES ('Hello World')),
    constant_2_int AS (VALUES (100))
SELECT *
FROM some_table
WHERE table_column = (table constant_1_str)
LIMIT (table constant_2_int)

Alternatywnie możesz użyć SELECT * FROM constant_namezamiast tego, TABLE constant_namektóry może być niepoprawny dla innych języków zapytań innych niż postgresql.

Oto przykład z użyciem instrukcji PREPARE . Nadal nie możesz używać ?, ale możesz używać $nnotacji:

PREPARE foo(integer) AS
    SELECT  *
    FROM    somewhere
    WHERE   something = $1;
EXECUTE foo(5);
DEALLOCATE foo;

To prawda, że ​​nie ma wyraźnego i jednoznacznego sposobu zadeklarowania zmiennej o jednej wartości, co możesz zrobić

with myVar as (select "any value really")

następnie, aby uzyskać dostęp do wartości przechowywanej w tej konstrukcji, robisz to

(select * from myVar)

na przykład

with var as (select 123)    
... where id = (select * from var)

Możesz skorzystać z funkcji specjalnych narzędzi. Podobnie jak w przypadku własnej własności składni DBeaver:

@set name = 'me'
SELECT :name;
SELECT ${name};

DELETE FROM book b
WHERE b.author_id IN (SELECT a.id FROM author AS a WHERE a.name = :name);

W DBeaver możesz używać parametrów w zapytaniach, tak jak możesz to zrobić z kodu, więc to zadziała:

SELECT *
FROM somewhere
WHERE something = :myvar

Po uruchomieniu zapytania DBeaver zapyta o wartość: myvar i uruchom zapytanie.