wake-up-neo.net

Eleganter Umgang mit PostgreSQL-Ausnahmen?

In PostgreSQL möchte ich einen Safe-Wrapping-Mechanismus erstellen, der ein leeres Ergebnis zurückgibt, wenn eine Ausnahme auftritt. Folgendes berücksichtigen:

SELECT * FROM myschema.mytable;

Ich könnte das Safe-Wrapping in der Client-Anwendung durchführen:

try {
    result = execute_query('SELECT value FROM myschema.mytable').fetchall();
}
catch(pg_exception) {
    result = []
}

Aber könnte ich so etwas direkt in SQL machen? Ich möchte, dass der folgende Code funktioniert, aber es scheint, als sollte er in den DO $$ ... $$-Block eingefügt werden.

BEGIN
    SELECT * FROM myschema.mytable;
EXCEPTION WHEN others THEN
    SELECT unnest(ARRAY[]::TEXT[])
END
sqlpostgresqlexception-handlingplpgsqldynamic-sql
13
Tregoreg

Ausnahmebehandlung in PL/pgSQL

Im Allgemeinen wird plpgsql-Code immer in einen BEGIN .. END-Block eingeschlossen. Dies kann im Rumpf einer DO-Anweisung oder einer Funktion liegen. Blöcke können innen verschachtelt sein - aber sie können nicht existieren outside, verwechseln Sie das nicht mit normalem SQL.

Jeder BEGIN-Block kann optional eine EXCEPTION-Klausel für die Behandlung von Ausnahmen enthalten. Funktionen, die Ausnahmen abfangen müssen, sind jedoch erheblich teurer. Ausnahmen sollten daher a priori vermieden werden.

Mehr Informationen:

How to Vermeiden eine Ausnahme im Beispiel

Eine DO -Anweisung kann nichts zurückgeben. Erstellen Sie eine function , die den Namen der Tabelle und des Schemas als Parameter übernimmt und gibt zurück, was Sie möchten:

CREATE OR REPLACE FUNCTION f_tbl_value(_tbl text, _schema text = 'public')
  RETURNS TABLE (value text) AS
$func$
DECLARE
   _t regclass := to_regclass(_schema || '.' || _tbl);
BEGIN
   IF _t IS NULL THEN
      value := ''; RETURN NEXT;    -- return single empty string
   ELSE
      RETURN QUERY EXECUTE
      'SELECT value FROM ' || _t;  -- return set of values
   END
$func$ LANGUAGE plpgsql;

Anruf: 

SELECT * FROM f_tbl_value('my_table');

Oder:

SELECT * FROM f_tbl_value('my_table', 'my_schema');

  • Angenommen, Sie möchten eine Gruppe von Zeilen mit einer einzelnen text-Spalte oder einer leeren Zeichenfolge, wenn die Tabelle nicht vorhanden ist.

  • Vorausgesetzt, dass eine Spalte value vorhanden ist, wenn die angegebene Tabelle vorhanden ist. Sie könnten das auch testen, aber Sie haben nicht danach gefragt.

  • Beide Parameter sind case sensitivetext -Werte. Dies unterscheidet sich geringfügig von der Art und Weise, wie Bezeichner in SQL-Anweisungen behandelt werden . Wenn Sie niemals Bezeichner in doppelte Anführungszeichen setzen, übergeben Sie Kleinbuchstaben, und es geht Ihnen gut.

  • Der Schemaname ist in meinem Beispiel standardmäßig 'public'. Passen Sie sich Ihren Bedürfnissen an. Sie können das Schema sogar vollständig ignorieren und standardmäßig den aktuellen search_path verwenden.

  • to_regclass() ist neu in Postgres 9.4. Für ältere Versionen ersetzen:

    IF EXISTS (
   SELECT 1
   FROM   information_schema.tables 
   WHERE  table_schema = _schema
   AND    table_name = _tbl
);

    Dies ist eigentlich genauer, da genau getestet wird, was Sie benötigen. __ Weitere Optionen und ausführliche Erklärung:

  • Bei der Arbeit mit dynamischem SQL immer gegen SQL-Injektion verteidigen! Die Besetzung in regclass macht hier den Trick. Mehr Details:

10
Erwin Brandstetter

Wenn Sie nur eine Spalte auswählen, sollte die Funktion COALESCE () in der Lage sein, den Trick für Sie auszuführen

SELECT COALESCE( value, '{}'::text[] ) FROM myschema.mytable

Wenn Sie mehr Zeilen benötigen, müssen Sie möglicherweise eine Funktion mit Typen erstellen.

0
Lucas