Różnica między językiem sql a językiem plpgsql w funkcjach PostgreSQL

Jestem bardzo nowy w tworzenie baz danych więc mam pewne wątpliwości dotyczące mojego poniższego przykładu:

Funkcja f1() - język sql

 create or replace function f1(istr  varchar) returns text as $$ 
 select 'hello! '::varchar || istr;
 $$ language sql;

Funkcja f2() - język plpgsql

 create  or replace function f2(istr  varchar)
 returns text as $$ 
 begin select 'hello! '::varchar || istr; end;
 $$ language plpgsql;

  • Obie funkcje można wywołać w postaci select f1('world') lub select f2('world').

  • Jeśli wywołam select f1('world') Wyjście będzie następujące: 

    `hello! world`

  • I wyjście dla select f2('world'):

    Błąd: zapytanie nie ma miejsca docelowego dla danych wyniku Podpowiedź: jeśli chcesz odrzucić wyniki zaznaczenia, użyj zamiast tego opcji Wykonaj. CONTEXT: PL / pgSQL function f11 (character varying) line 2 at SQL statement ****** błąd ******

  • Chcę poznać różnicę i w jakich sytuacjach powinienem użyć language sql lub language plpgsql.

Wszelkie przydatne linki lub odpowiedzi dotyczące funkcji będą mile widziane.

Author: Erwin Brandstetter, 2014-07-15
Source

2 answers

Funkcje SQL

Są lepszym wyborem:

  • Dla prostych zapytań skalarnych . Nie ma co planować, lepiej oszczędzaj na kosztach.

  • Dla pojedynczych wywołań na sesję . Nie ma nic do zyskania dzięki planowaniu buforowania i przygotowanym poleceniom, które PL/pgSQL ma do zaoferowania. Patrz poniżej.

  • Jeśli są one zwykle wywoływane w kontekście większych zapytań i są na tyle proste, aby być inlined.

  • Z braku doświadczenia z jakimkolwiek językiem proceduralnym, takim jak PL / pgSQL. Wielu dobrze zna SQL i to wszystko, czego potrzebujesz do funkcji SQL. Mało kto może powiedzieć to samo o PL / pgSQL.

  • Trochę krótszy kod. Nie ma bloku nad głową.

Funkcje PL / pgSQL

Są lepszym wyborem:

  • Gdy potrzebne są elementy proceduralne lub zmienne , które nie są dostępne oczywiście w funkcjach SQL.

  • Dla każdego rodzaju dynamicznego SQL , gdzie budujesz i EXECUTE wypowiedzi dynamicznie. Należy zachować szczególną ostrożność, aby uniknąć SQL injection. Więcej szczegółów:

  • Gdy masz obliczenia , które mogą być ponownie użyte w kilku miejscach, a CTE nie może być rozciągnięte do tego celu. W funkcji SQL nie masz zmiennych i będzie zmuszony do wielokrotnego obliczania lub zapisu do tabeli. Ta związana odpowiedź na dba.SE ma side-by-side przykłady kodu do rozwiązania tego samego problemu za pomocą funkcji SQL / funkcji plpgsql/zapytania z CTEs:

    Zadania są nieco droższe niż w innych językach proceduralnych. Dostosuj styl programowania, który nie używa więcej zadań niż jest to konieczne.

  • Gdy a funkcja nie może być inlinowana i jest wywoływana wielokrotnie. W przeciwieństwie do funkcji SQL, plany zapytań mogą być buforowane dla wszystkich instrukcji SQL wewnątrz funkcji PL/pgSQL; są one traktowane jak przygotowane instrukcje, plan jest buforowany dla powtarzających się wywołań w ramach tej samej sesji (jeśli Postgres oczekuje, że buforowany (ogólny) plan będzie działał lepiej niż ponowne planowanie za każdym razem. Dlatego funkcje PL / pgSQL są zazwyczaj szybciej po pierwszej parze wezwania w takich przypadkach.

    Oto wątek na pgsql-performance omawiający niektóre z tych elementów:
    Re: funkcje pl/pgsql przewyższają sql?

  • Kiedy trzeba błędy trap .

  • Na procedury wyzwalania (które też są tylko funkcjami).

  • Przy włączaniu poleceń DDL zmieniających obiekty lub zmieniających katalogi systemowe w jakikolwiek sposób istotne dla kolejnych poleceń-ponieważ wszystkie polecenia w funkcjach SQL są przetwarzane jednocześnie, podczas gdy funkcje PL / pgSQL planują i wykonują każde polecenie sekwencyjnie (jak przygotowane polecenie). Zobacz:

Rozważ również:

Dla kompletności: aby faktycznie zwrócić z funkcji PL / pgSQL, można napisz: 

CREATE FUNCTION f2(istr varchar)
  RETURNS text AS
$func$
BEGIN
   RETURN 'hello! ';  -- defaults to type text anyway
END
$func$ LANGUAGE plpgsql;

Są inne sposoby:

 32
Author: Erwin Brandstetter,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-06-26 02:11:42

PL / PgSQL jest specyficznym dla PostgreSQL językiem proceduralnym opartym na SQL . Posiada pętle, zmienne, obsługę błędów/WYJĄTKÓW, itd. Nie wszystkie SQL są poprawne PL / PgSQL - jak odkryłeś, na przykład nie możesz używać SELECT Bez INTO lub RETURN QUERY. PL / PgSQL może być również używany w blokach DO do procedur jednorazowych.

sql Funkcje mogą używać tylko czystego SQL, ale często są bardziej wydajne i łatwiejsze do napisania, ponieważ nie potrzebujesz BEGIN ... END; bloku itp. Funkcje SQL może być inlined, co nie jest prawdą dla PL / PgSQL.

Ludzie często używają PL / PgSQL, gdzie zwykły SQL byłby wystarczający, ponieważ są przyzwyczajeni do proceduralnego myślenia. W większości przypadków, gdy wydaje ci się, że potrzebujesz PL/PgSQL, prawdopodobnie nie. rekurencyjne CTE, zapytania boczne itp.Generalnie zaspokajają większość potrzeb.

Aby uzyskać więcej informacji ... zobacz instrukcję.

 12
Author: Craig Ringer,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2014-07-15 10:31:13