Jak uniknąć wielokrotnych błędów funkcji za pomocą składni (func ()). * W zapytaniu SQL?
Gdy funkcja zwraca aTABLE
lub aSETOF composite-type
, podobnie jak ta przykładowa funkcja:
CREATE FUNCTION func(n int) returns table(i int, j bigint) as $
BEGIN
RETURN QUERY select 1,n::bigint
union all select 2,n*n::bigint
union all select 3,n*n*n::bigint;
END
$ language plpgsql;
wyniki można uzyskać za pomocą różnych metod:
1)select * from func(3)
wytworzy te kolumny wyjściowe:
i | j ---+--- 1 | 3 2 | 9 3 | 27
2)select func(3)
wytworzy tylko jedną kolumnę wyjściową typu ROW.
func ------- (1,3) (2,9) (3,27)
3)select (func(3)).*
będzie produkować jak # 1:
i | j ---+--- 1 | 3 2 | 9 3 | 27
Gdy argument funkcji pochodzi z tabeli lub podzapytania, składnia # 3 jest jedyną możliwą, jak w:
select N, (func(N)).* from (select 2 as N union select 3 as N) s;
lub jak w tym powiązanymodpowiedź. Gdybyśmy mieliLATERAL JOIN
moglibyśmy to wykorzystać, ale dopóki PostgreSQL 9.3 nie będzie dostępny, nie jest on obsługiwany, a poprzednie wersje i tak będą używane przez lata.
Teraz problem ze składnią # 3 polega na tym, że funkcja jest wywoływana tyle razy, ile jest kolumn w wyniku. Nie ma na to wyraźnego powodu, ale tak się dzieje. Możemy to zobaczyć w wersji 9.2, dodającRAISE NOTICE 'called for %', n
w funkcji. Przy powyższym zapytaniu wyświetla:
NOTICE: called for 2 NOTICE: called for 2 NOTICE: called for 3 NOTICE: called for 3
Teraz, jeśli funkcja zostanie zmieniona, aby zwrócić 4 kolumny, jak poniżej:
CREATE FUNCTION func(n int) returns table(i int, j bigint,k int, l int) as $
BEGIN
raise notice 'called for %', n;
RETURN QUERY select 1,n::bigint,1,1
union all select 2,n*n::bigint,1,1
union all select 3,n*n*n::bigint,1,1;
END
$ language plpgsql stable;
następnie te same dane wyjściowe zapytania:
NOTICE: called for 2 NOTICE: called for 2 NOTICE: called for 2 NOTICE: called for 2 NOTICE: called for 3 NOTICE: called for 3 NOTICE: called for 3 NOTICE: called for 3
Potrzebne były 2 wywołania funkcji, 8 zostało faktycznie wykonanych. Stosunek to liczba kolumn wyjściowych.
Dzięki składni # 2, która daje taki sam wynik, z wyjątkiem układu kolumn wyjściowych, te wielokrotne wywołania się nie zdarzają:
select N,func(N) from (select 2 as N union select 3 as N) s;
daje:
NOTICE: called for 2 NOTICE: called for 3
a następnie 6 wynikowych wierszy:
n | func ---+------------ 2 | (1,2,1,1) 2 | (2,4,1,1) 2 | (3,8,1,1) 3 | (1,3,1,1) 3 | (2,9,1,1) 3 | (3,27,1,1)pytania
Czy istnieje składnia lub konstrukcja z 9.2, która osiągnie oczekiwany wynik, wykonując tylko minimalne wymagane wywołania funkcji?
Pytanie bonusowe: dlaczego wielokrotne oceny w ogóle się zdarzają?