Ja natürlich. Das ist die Hauptaufgabe einer Funktion: eine Eingabe entgegennehmen und einen andere Wert zurückliefern. z.B numerische Funktionen wie abs(), floor() aber natürlich aus String Funktionen wie upper() oder substr()
Edit: hatte übersehen, dass Du Menge geschrieben hast. Ja, auch das ist möglich wenn funktion() ein Resultat zurückliefert. z.B. unnest() oder regexp_split_to_table()
Mit geht es primär um die Frage ob die Funktion die Ergebnismenge reduzieren kann (filtern)?
Konkretes Beispiel worüber ich gestolpert bin und was der Grund für die Frage war:
SELECT
tmp.id
,tmp.t
,(REGEXP_MATCHES(tmp.t,‘^(A)(..)$’,‘g’))[2]
FROM (SELECT 1 AS id,‘ABC’ AS t UNION SELECT 2,‘BBC’) tmp;
Dieses SQL-Statement liefert 1 Zeile als Ergebnismenge:
id | t | regexp_matches
----±----±---------------
1 | ABC | BC
(1 Zeile)
erwartet hatte ich:
id | t | regexp_matches
----±----±---------------
1 | ABC | BC
2 | BBC |
(2 Zeilen)
Die Funktion REGEXP_MATCHES() filtert die Zeile mit der id=2 aus. Ich hatte erwartet, dass die Spalte mit dem Wert für diese Funktion dann NULL sein sollte und die Zeile in der Ergebnismenge erhalten bleibt.
Ist das ein gewolltes Verhalten oder habe ich hier einen Denkfehler ?
Eine Set-Returning-Function in der SELECT Liste ist äquivalent zu einem CROSS JOIN:
SELECT tmp.id, tmp.t, r.x[2]
FROM (
values (1,'ABC'), (2, 'BBC')
) as tmp(id, t)
cross join regexp_matches(tmp.t,'^(A)(..)$','g') as r(x)
Der CROSS JOIN wird aber nur dann einen Datensatz liefern wenn die Quelle einen liefert. Das Verhalten was Du erwartest wäre ein LEFT JOIN:
SELECT tmp.id, tmp.t, r.x[2]
FROM (
values (1,'ABC'), (2, 'BBC')
) as tmp(id, t)
left join regexp_matches(tmp.t,'^(A)(..)$','g') as r(x) on true
Ich persönlich finde die Verwendung einer solchen Funktion in der SELECT Liste immer merkwürdig. Letztendlich ist es ähnlich wie die folgende (nicht erlaubten) Abfrage:
SELECT st.id, (SELECT ot.* FROM other_table)
FROM some_table st
SELECT st.id, (SELECT ot.* FROM other_table)
FROM some_table st
müsste ausführbar sein sofern die innere Abfrage genau einen oder keinen Wert liefert.
In meiner Abfrage ((REGEXP_MATCHES(tmp.t,‘^(A)(..)$’,‘g’))[2]) greife ich ja auf ein Element vom Typ text zu , das zu dem Zeitpunkt einen oder keinen definierten Wert hat (für jede Zeile in der Ergebnismenge). Es ist ja ein einfacher Datentyp und kein Set was da in der SELECT Liste ausgegeben werden soll.
Weiß jemand ob das im SQL-Standard so definiert ist oder ob das eine Postgre Erweiterung ist mit der Umsetzung als quasi “cross join” ?
Ja natürlich, aber eine SRF liefert nun mal mehrere Spalten und mehrere Zeilen. Wenn ich das innere SELECT als SELECT ot.column_one, ot.column_two schreib dann ist die Diskrepanz offensichtlicher
Jein. Du greifst auf eine Spalte eines Resultats mehrerer Zeilen zu. Genau wegen diesen Unklarheiten finde ich die Verwendung von SRF in der SELECT Liste problematisch. Wenn man das als CROSS/LEFT JOIN schreibt, ist automatisch klar was da eigentlich passiert.
Soweit ich weiß, ist das im SQL Standard überhaupt nicht erlaubt.