returns setof / Tabelle aus Funktion zurückgeben

Hallo,

habe leider bisher keine Antwort auf folgende Fragestellung gefunden: Möchte in einer Funktion ein Select Statement ausführen welches 1 bis n Zeilen zurückgibt und diese dann als Tabelle zurückgeben.

Die Tabellendefinition

 
CREATE TABLE dev."Folders"
(
  "FolderID" integer NOT NULL DEFAULT nextval('dev."SQ_Folders_FolderID"'::regclass),
  "Name" character varying(255),
  "MPTTLeft" integer NOT NULL,
  "MPTTRight" integer NOT NULL,
  CONSTRAINT "PK_Folders" PRIMARY KEY ("FolderID")
)
WITH (
  OIDS=FALSE
);

Tabelleninhalt

 
FolderID, Name, MPTTLeft, MPTTRight
0;"root";1;6
1;"Folder1";2;5
2;"Folder1.1";3;4

und der Versuch einer Funktionsdeklaration

 
CREATE OR REPLACE FUNCTION dev."sfGetFolderParents"(integer)
  RETURNS setof record AS
$BODY$
   DECLARE
      "vcName"          varchar(10);
      "iMPTTLeftValue"  int8;
      "iMPTTRightValue" int8;
   BEGIN
      SELECT "MPTTLeft", "MPTTRight" INTO "iMPTTLeftValue", "iMPTTRightValue" FROM dev."Folders" WHERE "FolderID" = $1;
      SELECT "Name" INTO "vcName" FROM dev."Folders" WHERE "MPTTLeft" < "iMPTTLeftValue" AND "MPTTRight" > "iMPTTRightValue" ORDER BY "MPTTLeft" ASC;
      RETURN NEXT;
   END;
   $BODY$
  LANGUAGE 'plpgsql' VOLATILE

der Funktionsaufruf

 
select dev."sfGetFolderParents"(2);

sollte folgende Tabelle zurückgeben:

 
root
Folder1

Danke

Nun ja, du schreibst zwar die SELECTs, aber wohin wandern die Ergebnisse der Anfragen? Dafür gibt es SELECT … INTO. Außerdem musst du, wenn du mehr als ein Ergebnis erwartest, eine Schleife darum legen.

Beides ist mit Beispielen in der Dokumentation zu plpgsql beschrieben.

Hallo,

also Select … Into verwende ich ja. Das mit der Schleife drumherum muss ich mir dann noch anschauen. Hatte in die Doku geschaut aber eigentlich kein Bespiel gefunden in dem ein Select verwendet wurde…

Danke

Mehrere Fehler. Das beginnt beim Aufruf der Funktion. Oder nein, das beginnt schon damit, daß Du uns keine Fehlermeldung zeigts. Okay. Funktionsaufruf. Deine Funktion liefert mehrere Felder. Oder soll sie zumindest liefern. Welche davon hättest Du denn gerne im Resultat? Du nennst keines der Felder.

Nun zur Funktion selber. Da sind 2 Selects. Diese liefern (möglicherweise) 2 unterschiedlich große Resultate. Deine Art diese zu verknüpfen wird so nicht klappen (ungetestet).

Was willst Du eigentlich erreichen? Eine rekursive Abfrage? Dazu gibt es etliches an Beispielen, guggst Du z.B. bei Depesz ( select * from depesz; ) oder wartest auf 8.4.

Um bis dahin mal mit Funktionen und SRF’s zu spielen dieses Beispiel:

test=# create table foo (id serial,name text);
NOTICE:  CREATE TABLE will create implicit sequence "foo_id_seq" for serial column "foo.id"
CREATE TABLE                                                                               
Zeit: 4,851 ms                                                                             
test=*# copy foo (name) from stdin;
Geben Sie die zu kopierenden Daten ein, gefolgt von einem Zeilenende.
Beenden Sie mit einem Backslash und einem Punkt alleine auf einer Zeile.
>> andreas
>> anja
>> magda
>> katha
>> tamara
>> \.
Zeit: 11673,464 ms
test=*# create or replace function get_foo(out out_id int, out out_name text) returns setof record as $$declare rec record; begin for rec in select * from foo loop out_id:=rec.id;out_name:=rec.name; return next; end loop; end; $$language plpgsql;
CREATE FUNCTION
Zeit: 0,484 ms
test=*# select * from get_foo();
 out_id | out_name
--------+----------
      1 | andreas
      2 | anja
      3 | magda
      4 | katha
      5 | tamara
(5 Zeilen)

Zeit: 0,387 ms

Andreas

PostgreSQL: Documentation: Manuals: PostgreSQL 8.3: Control Structures

Andreas

Die Funktion soll letztendlich eine einfache Liste mit Foldernamen zurückgeben, d.h. die Tabelle die zurückgegeben werden soll hat nur eine Spalte.
Übergabewert an die Funktion ist die ID des Folders dessen Eltern gesucht werden sollen. Nein, ich will keine rekursive Funktion, sondern einen “modified pre-ordered tree traversal”, deswegen die left und right values die im ersten select für den Startfolder ermittelt werden soll. Das erste Select liefert übrigens immer genau einen Satz/Wert zurück (FolderID in der where clause ist einziges Feld im primary key der Tabelle). Das zweite Select liefert dann alle Parents (ohne Rekursion).

Danke für das Beispiel und den Link zur Dokumentationseite.

Hallo,

CREATE FUNCTION bringt nach Überarbeitung folgende Fehlermeldung:

ERROR:  function result type must be character varying because of OUT parameters

********** Fehler **********

ERROR: function result type must be character varying because of OUT parameters
SQL Status:42P13

Die Funktion sieht jetzt nach “studium” der Beispielfunktion und Dokumentation so aus:

CREATE OR REPLACE FUNCTION dev."sfGetFolderParents"(in "iFolderID" integer, out "vcFolderName" varchar(255))
  RETURNS SETOF record AS
$BODY$
   DECLARE 
      rec               record;
      "vcFolderName"    varchar(255);
      "iMPTTLeftValue"  int8;
      "iMPTTRightValue" int8;
   BEGIN
      -- get left and right value of folder passed to function
      SELECT "MPTTLeft", "MPTTRight" INTO "iMPTTLeftValue", "iMPTTRightValue" FROM dev."Folders" WHERE "FolderID" = $1;

      -- select all parents
      FOR rec IN SELECT "Name" FROM dev."Folders" WHERE "MPTTLeft" < "iMPTTLeftValue" AND "MPTTRight" > "iMPTTRightValue" ORDER BY "MPTTLeft" ASC LOOP "vcFolderName":=rec."Name";
      RETURN NEXT;
      END LOOP;
      RETURN;
   END;
   $BODY$
   LANGUAGE 'plpgsql' VOLATILE

Über eine nochmalige Antwort würde ich mich freuen.

Danke

Nun, entweder du lieferst RECORD zurück oder du verwendest OUT Parameter.
Sind dir die Unterschiede zwischen beiden Möglichkeiten bewusst?

Ehrlich gesagt nein. Hatte mir das Beispiel von akretschmer oben angeschaut und das sah mir nicht so aus als wenn die Verwendung von out und record sich ausschliessen?

Tut es auch nicht. Dein Fehler ist, daß Dein OUT-Parameter und eine interne Variable den selben Namen haben. Mach Dir nix draus, darüber sind schon viele gestolpert…

Andreas