Wie man eine Variable in einer PostgreSQL-Abfrage deklariert

Question

Wie deklariere ich eine Variable zur Verwendung in einer PostgreSQL 8.3-Abfrage?

In MS SQL Server kann ich dies tun:

DECLARE @myvar INT
SET @myvar = 5

SELECT *
FROM somewhere
WHERE something = @myvar

Wie kann ich dasselbe in PostgreSQL tun? Laut der Dokumentation werden Variablen einfach als "name type;" deklariert, aber das gibt mir einen Syntaxfehler:

myvar INTEGER;

Kann mir jemand ein Beispiel für die richtige Syntax geben?

Answer 1

Diese Lösung basiert auf dem Vorschlag von Fei0x hat aber den Vorteil, dass die Werteliste der Konstanten in der Abfrage nicht verbunden werden muss und die Konstanten einfach am Anfang der Abfrage aufgelistet werden können. Sie funktioniert auch in rekursiven Abfragen.

Im Grunde genommen ist jede Konstante eine einwertige Tabelle erklärt in einer WITH-Klausel, die dann an beliebiger Stelle im restlichen Teil der Abfrage aufgerufen werden kann.

• Einfaches Beispiel mit zwei Konstanten:

  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)

Alternativ können Sie auch SELECT * FROM constant_name anstelle von TABLE constant_name die möglicherweise für andere Abfragesprachen als Postgresql nicht gültig sind.

Answer 2

Verwendung einer Temp-Tabelle außerhalb von pl/PgSQL

Abgesehen von der Verwendung von pl/pgsql oder einer anderen pl/*-Sprache, wie vorgeschlagen, ist dies die einzige andere Möglichkeit, die mir einfällt.

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

Answer 3

Ich möchte eine Verbesserung der folgenden Punkte vorschlagen @DarioBarrionuevos Antwort um die Nutzung von temporären Tabellen zu vereinfachen.

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;

Answer 4

Wie Sie den anderen Antworten entnommen haben, verfügt PostgreSQL nicht über diesen Mechanismus in direktem SQL, obwohl Sie jetzt eine anonymer Block . Sie können jedoch etwas Ähnliches mit einem Common Table Expression (CTE) machen:

WITH vars AS (
    SELECT 5 AS myvar
)
SELECT *
FROM somewhere,vars
WHERE something = vars.myvar;

Sie können natürlich so viele Variablen haben, wie Sie wollen, und sie können auch abgeleitet werden. Zum Beispiel:

WITH vars AS (
    SELECT
        '1980-01-01'::date AS start,
        '1999-12-31'::date AS end,
        (SELECT avg(height) FROM customers) AS avg_height
)
SELECT *
FROM customers,vars
WHERE (dob BETWEEN vars.start AND vars.end) AND height<vars.avg_height;

Der Prozess ist:

• Erzeugen Sie eine einzeilige Cte mit SELECT ohne eine Tabelle (in Oracle müssen Sie die FROM DUAL ).
• CROSS JOIN der cte mit der anderen Tabelle. Obwohl es eine CROSS JOIN Syntax ist die ältere Komma-Syntax etwas besser lesbar.
• Beachten Sie, dass ich die Daten gecastet habe, um mögliche Probleme in der Datenbank zu vermeiden. SELECT Klausel. Ich habe die kürzere Syntax von PostgreSQL verwendet, aber Sie hätten auch die formalere CAST('1980-01-01' AS date) für dialektübergreifende Kompatibilität.

Normalerweise sollte man Cross-Joins vermeiden, aber da man nur eine einzige Zeile kreuzt, hat dies den Effekt, dass die Tabelle einfach um die variablen Daten erweitert wird.

In vielen Fällen ist es nicht erforderlich, die vars. Präfix, wenn die Namen nicht mit den Namen in der anderen Tabelle kollidieren. Ich füge es hier ein, um den Punkt zu verdeutlichen.

Außerdem können Sie weitere CTEs hinzufügen.

Dies funktioniert auch in allen aktuellen Versionen von MSSQL und MySQL, die Variablen unterstützen, sowie in SQLite, das dies nicht tut, und in Oracle, das dies in gewisser Weise tut und in gewisser Weise nicht tut.

Answer 5

Sie können auf besondere Funktionen des Werkzeugs zurückgreifen. Wie für DBeaver eigene proprietäre Syntax:

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