Cursor-basierte Datensätze in PostgreSQL

Ich versuche, verwenden Sie Cursor für eine Abfrage, die joins von mehreren Tabellen. Ich habe gesehen, dass bei oracle gibt es eine cursor-basierten Datensatz. Wenn ich versuche, das gleiche für Postgres, wirft es einige Fehler. Wie kann ich das gleiche tun in Postgres?

CREATE OR REPLACE FUNCTION avoidable_states()
RETURNS SETOF varchar AS
$BODY$
DECLARE
    xyz CURSOR FOR select * from address ad
                            join city ct on ad.city_id = ct.city_id;    
    xyz_row RECORD;
BEGIN   
    open xyz;

    LOOP
    fetch xyz into xyz_row;
        exit when xyz_row = null;
        if xyz_row.city like '%hi%' then
            return next xyz_row.city;               
        end if;
    END LOOP;
    close xyz;  
END;
$BODY$
  LANGUAGE plpgsql VOLATILE;

Fehler den ich bekomme, ist:

ERROR:  relation "xyz" does not exist
CONTEXT:  compilation of PL/pgSQL function "avoidable_states" near line 4
InformationsquelleAutor Zeus | 2014-03-12
  1. 4

    Verwenden Sie einfach die RECORD Typ:

    DECLARE
    ...
    cur_row RECORD;
BEGIN
    ...
    FETCH xyz INTO cur_row;
    EXIT WHEN NOT FOUND;
    IF cur_row.city LIKE 'CH%' THEN
        ...
    • Vielen Dank, dass hat funktioniert. Aber, wenn in der Schleife, wie kann ich break aus der Schleife heraus? sagen, ich verwendet, beendet wird, wenn cur_row = null ; es bricht aber nicht aus. Ich aktualisiert die ursprüngliche Frage mit dem neuen code.
    • Ich aktualisierte die Antwort zu zeigen, was Sie wollen. Finden Sie Erwin ' s Antwort, als Sie zeigt, einige andere (und in der Regel besser IMHO) Möglichkeiten der Iteration über eine Abfrage, obwohl manchmal benötigen wir einen cursor, so...
    InformationsquelleAutor MatheusOl
  2. 32

    1. Implizite cursor

    Ist es fast immer besser, um die implizite cursor von einem FOR loop als resort zu einem etwas langsameren und unhandlich expliziten cursor. Ich habe geschrieben, Tausende von plpgsql-Funktionen und nur eine hand voll von Zeiten explizite Cursor keinen Sinn gemacht.

    CREATE OR REPLACE FUNCTION avoidable_states()
  RETURNS SETOF varchar AS
$func$
DECLARE
    rec record;
BEGIN   
   FOR rec IN
      SELECT *
      FROM   address ad
      JOIN   city    ct USING (city_id)
   LOOP
      IF rec.city LIKE '%hi%' THEN
          RETURN NEXT rec.city;               
      END IF;
   END LOOP;
END
$func$  LANGUAGE plpgsql STABLE;

    Beiseite: es gibt nichts in der Funktion, die Sie brauchen würden Volatilität VOLATILE. Verwenden STABLE.

    2. Set-basierten Ansatz

    Ist es fast immer besser, einen set-basierten Ansatz wenn möglich. Verwenden RETURN QUERY zurückkehren, wie aus einer Abfrage direkt.

    CREATE OR REPLACE FUNCTION avoidable_states()
  RETURNS SETOF varchar AS
$func$
BEGIN   
   RETURN QUERY
   SELECT ct.city
   FROM   address ad
   JOIN   city    ct USING (city_id)
   WHERE  ct.city LIKE '%hi%';
END
$func$  LANGUAGE plpgsql STABLE;

    3. SQL-Funktion

    Für den einfachen Fall (wahrscheinlich eine Vereinfachung), könnte man auch mit einem einfachen SQL-Funktion oder auch nur die Abfrage:

    CREATE OR REPLACE FUNCTION avoidable_states()
  RETURNS SETOF varchar AS
$func$
   SELECT ct.city
   FROM   address ad
   JOIN   city    ct USING (city_id)
   WHERE  ct.city LIKE '%hi%';
$func$  LANGUAGE sql STABLE;
    • Danke, schöne Erklärung
    • Ich komme aus einer Oracle-PL/SQL-hintergrund, und ich Frage mich, ob es einen Weg gibt, Sie zu "verstecken" implizite cursor in die DECLARE Teil der Funktion, klicken Sie dann verweisen Sie ihn nur per cursor-name in der FOR - Schleife? Selbstverständlich, dass wenn die select-Anweisung ist nur ein paar Zeilen code, es macht nicht viel Unterschied. Aber wenn die SELECT Aussage ist 50 Zeilen code, dann ist es wirklich störend (für debugging Zwecke) zu haben, um SQL in Ihrem prozeduralen code.
    InformationsquelleAutor Erwin Brandstetter
Schreibe einen Kommentar