Geben Sie mehrere Felder als Datensatz in PostgreSQL mit PL / pgSQL zurück
Schreibe ich ein SP mit PL/pgSQL.
Ich will die Rückgabe eines Datensatzes, bestehend aus Feldern aus verschiedenen Tabellen. Könnte in etwa so Aussehen:
CREATE OR REPLACE FUNCTION get_object_fields(name text)
RETURNS RECORD AS $$
BEGIN
-- fetch fields f1, f2 and f3 from table t1
-- fetch fields f4, f5 from table t2
-- fetch fields f6, f7 and f8 from table t3
-- return fields f1 ... f8 as a record
END
$$ language plpgsql;
Wie kann ich wieder die Felder aus verschiedenen Tabellen als Felder in einem Datensatz?
[Bearbeiten]
Habe ich gemerkt, dass das Beispiel gab ich oben war etwas zu simpel. Einige der Felder, die ich brauche, um abgerufen werden, gespeichert werden als separate Zeilen in der Datenbank-Tabelle abgefragt wird, aber ich wollen, kehren Sie in die 'abgeflacht' Datensatz-Struktur.
Den code unten soll veranschaulichen, weitere:
CREATE TABLE user (id int, school_id int, name varchar(32));
CREATE TYPE my_type (
user1_id int,
user1_name varchar(32),
user2_id int,
user2_name varchar(32)
);
CREATE OR REPLACE FUNCTION get_two_users_from_school(schoolid int)
RETURNS my_type AS $$
DECLARE
result my_type;
temp_result user;
BEGIN
-- for purpose of this question assume 2 rows returned
SELECT id, name INTO temp_result FROM user where school_id = schoolid LIMIT 2;
-- Will the (pseudo)code below work?:
result.user1_id := temp_result[0].id ;
result.user1_name := temp_result[0].name ;
result.user2_id := temp_result[1].id ;
result.user2_name := temp_result[1].name ;
return result ;
END
$$ language plpgsql
InformationsquelleAutor der Frage skyeagle | 2010-12-28
Du musst angemeldet sein, um einen Kommentar abzugeben.
Müssen Sie definieren eine neue Art und definieren Sie Ihre Funktion zurück.
Wenn Sie zurückkehren möchten mehr als einen Datensatz, den Sie brauchen, um zu definieren die Funktion als
returns setof my_type
Update
Andere Möglichkeit ist die Verwendung
RETURNS TABLE()
anstatt eineTYPE
wurde in Postgres 8.4InformationsquelleAutor der Antwort a_horse_with_no_name
Nicht verwenden CREATE TYPEum wieder eine polymorphe Ergebnis. Gebrauch und Missbrauch der Datensatztyp statt. Check it out:
Achten Sie auf die Tatsache, dass es Optional Rückkehr zwei oder drei Spalten abhängig von der Eingabe.
Dies wirkt sich verheerend auf code, so dass eine konsistente Anzahl von Spalten, aber es ist wahnsinnig praktisch für die Rückkehr optionale Fehlermeldungen mit dem ersten parameter Rücksendung der Erfolg der operation. Geschrieben mit einer Konstanten Anzahl von Spalten:
Fast epische Schärfe:
Aber wie kann man die Spaltung, die sich in mehreren Reihen, so dass Ihre ORM-layer der Wahl können konvertieren Sie die Werte in Ihrer Sprache der Wahl nativen Datentypen? Die Schärfe:
Dies ist einer der coolsten und am meisten genutzte features in PostgreSQL. Bitte verbreiten Sie das Wort.
InformationsquelleAutor der Antwort Sean
Dies kann einfacher mit
- Parameter:
Du nicht müssen zu erstellen, geben Sie nur für die Zwecke dieser plpgsql-Funktion. Es kann nützlich sein, wenn Sie binden möchten ein paar Funktionen, die auf den gleichen Typ. Ich habe selten verwenden, es nicht mehr, da
OUT
Parameter wurden Hinzugefügt.Wie Sie vielleicht bemerkt haben, gibt es keine
RETURN
- Anweisung.OUT
Parameter werden automatisch zurückgegeben, keineRETURN
- Anweisung notwendig.Seit
OUT
Parameter sind sichtbar, überall innerhalb der Funktion Körper (und kann genauso verwendet werden wie jede andere variable), stellen Sie sicher, dass Tisch-qualifizieren Spalten mit dem gleichen Namen, um Namenskonflikte zu vermeiden.Einfacher noch - oder mehrere Zeilen zurückgeben
Meisten der Zeit, kann dies weiter vereinfacht werden. Manchmal sind Abfragen in der Funktion Körper, kombiniert werden können, die in der Regel (nicht immer) schneller. Und Sie können
RETURNS TABLE()
- eingeführt mit Postgres 8.4 (lange, bevor diese Frage gestellt wurde).Das obige Beispiel könnte wie folgt umgeschrieben werden:
RETURNS TABLE
ist effektiv das gleiche wie mit ein paarOUT
Parameter kombiniert mitRETURNS record
nur ein bisschen kürzer /eleganter.Der wesentliche Unterschied ist, dass diese Funktion return 0, 1 oder viele Zeilen, während die erste version immer liefert 1 Zeile.
Wenn Sie sicherstellen möchten, dieser gibt nur 0 oder 1 Zeile hinzufügen
LIMIT 1
wie gezeigt.RETURN QUERY
ist das sehr praktisch, modernen Weg, um Ergebnisse aus einer Abfrage direkt ein.Sie können mehrere Instanzen in einer einzigen Funktion, fügen Sie weitere Zeilen an die Ausgabe.
Verschiedenen row-Typen
Wenn Ihre Funktion soll dynamisch liefert Ergebnisse mit verschiedenen row-Typenje nach Eingabe, Lesen Sie hier mehr:
InformationsquelleAutor der Antwort Erwin Brandstetter
Wenn Sie eine Tabelle mit genau diesem Datensatz-layout, verwenden Sie seinen Namen geben, sonst haben Sie zu erklären, den Typ explizit:
InformationsquelleAutor der Antwort Quassnoi
hierzu können Sie mithilfe der OUT-parameter und CROSS JOIN
dann verwenden Sie es als eine Tabelle:
oder
oder
InformationsquelleAutor der Antwort Jerome RIVRON
Können Sie erreichen dies, indem Sie einfach als gibt Reihe von Datensätzen, mit return query.
Und rufen Sie diese Funktion als :
select * from schemaName.get_two_users_from_school(schoolid) as x(a bigint, b varchar);
InformationsquelleAutor der Antwort Ritesh Jha