postgres stored procedure - ghdrako/doc_snipets GitHub Wiki

Description: define a new procedural language
Syntax:
CREATE [ OR REPLACE ] [ TRUSTED ] [ PROCEDURAL ]
  LANGUAGE name
    HANDLER call_handler [ INLINE inline_handler ]
  [ VALIDATOR valfunction ]
CREATE [ OR REPLACE ] [ TRUSTED ] [ PROCEDURAL ]
  LANGUAGE name
  • HANDLER: This function is actually the glue between PostgreSQL and any external language that you want to use. It is in charge of mapping PostgreSQL data structures to whatever is needed by thelanguage and helps pass the code around.
  • VALIDATOR: This is the police officer of the infrastructure. If it is available, it will be in charge of delivering tasty syntax errors to the end user. Many languages are able to parse code before actually executing it. PostgreSQL can use that and tell you whether a function is correct or not when you create it. Unfortunately, not all languages can do this, so in some cases, you will still be left with problems showing up at runtime.
  • INLINE: If this is present, PostgreSQL will be able to run anonymous code blocks utilizing this handler function.

The following options are available and are shipped along with the PostgreSQL core:

  • SQL
  • PL/pgSQL
  • PL/Perl and PL/PerlU
  • PL/Python
  • PL/Tcl and PL/TclU

Trzy języki włączone domyślnie:

  • C
  • SQL
  • PL/pgSQL

trusted/untrasted language

The idea is that a trusted language is restricted to the very core of the language, therefore, it is not possible to do the following:

  • Include libraries
  • Open network sockets
  • Perform system calls of any kind, which would include opening files Perl offers something called taint mode, which is used to implementacja this feature in PostgreSQL. Perl will automatically restrict itself to trusted mode and error out if a security violation is about to happen. In untrusted mode, everything is possible; therefore, only the superuser is allowed to run untrusted code.If you want to run trusted as well as untrusted code, you have to activate both languages, that is, plperl and plperlu (pltcl and pltclu, respectively).

Trusted

  • Umożliwia tylko dostęp do bazy danych
  • nie do systemów plików, gniazd, sieci itp.
  • SQL,PL/pgSQL,PL/Perl,PL/Tcl Untrusted
  • PL/Python,C…
  • PL/TclU, PL/PerlU

Dostęp do bazy danych mają tylko zaufane języki - Trusted . Nie mogą uzyskać dostępu do innych baz danych, systemów plików, sieci itp. W związku z tym są one ograniczone, co sprawia, że trudniej jest ich użyć do naruszenia systemu. Typowym przykładem jest PL/pgSQL. Ale z tego powodu oferują mniej możliwości niż inne języki.

Tylko superużytkownicy mogą utworzyć procedurę w niezaufanym języku. Mogą jednak następnie nadać prawa do wykonywania tych procedur innym rolom w bazie danych:

GRANT EXECUTE ON FUNCTION my_function TO un_role ;

"Inne" języki PL, takie jak PL/perl10 i PL/Python (dwa najczęściej używane po PL/pgSQL), są znacznie bardziej zaawansowane niż PL/PgSQL. Na przykład są znacznie wydajniejsze w przetwarzaniu ciągów znaków, mają zaawansowane struktury, takie jak tabele skrótów, pozwalają na używanie zmiennych statycznych do utrzymywania pamięci podręcznej, a nawet, w przypadku swojej jednej wersji, mogą wykonywać wywołania systemowe. W takim przypadku możliwe staje się na przykład wywołanie usługi internetowej lub zapisanie danych do pliku zewnętrznego.

Istnieją specjalistyczne języki PL. Najbardziej charakterystycznym z nich jest PL/R. R to język używany przez statystyków do manipulowania dużymi zbiorami danych. PL/R pozwala zatem na wykonywanie tych procesów R bezpośrednio w bazie danych, co byłoby bardzo trudne do napisania w innych językach, oraz z opóźnieniami w przesyłaniu danych.

Możesz pisać procedury przechowywane bezpośrednio w C. Zostaną one skompilowane poza PostgreSQL, z poszanowaniem pewnego formalizmu, a następnie zostaną załadowane wskazując bibliotekę C, która je zawiera, oraz ich parametry i zwracane typy.

Ale uwaga: każdy błąd w kodzie prawdopodobnie uzyska dostęp do całej pamięci widocznej dla procesu PostgreSQL, który go wykonuje, a tym samym uszkodzi dane. Dlatego zaleca się to robić tylko w ostateczności

Duża wada jest prosta i wspólna dla wszystkich tych języków: nie są one specjalnie zaprojektowane do działania jako język procedur składowanych. Tak więc to, czego używasz, gdy piszesz PL/Perl, to kod Perla, z kilkoma dodatkowymi funkcjami (poprzedzonymi prefixem spi) umożliwiającymi dostęp do bazy danych; podobnie w sprawie C. Dostęp do danych jest dość żmudny składniowo w porównaniu do PL/pgSQL.

Innym problemem związanym z językami PL (innymi niż C i PL/pgSQL) jest to, że języki te nie mają tych samych typów natywnych co PostgreSQL i działają w stosunkowo oddzielnym interpreterze. Wydajność jest zatem niższa niż w przypadku PL/pgSQL i C, w przypadku przetwarzania, w którym najbardziej zużywany jest dostęp do danych. Często czas przetwarzania w jednym z tych bardziej zaawansowanych języków jest jeszcze lepszy dzięki czasowi zaoszczędzonemu przez inne funkcje (na przykład możliwość korzystania z pamięci podręcznej lub tabeli skrótów).

Programy napisane w językach PL są zwykle przechowywane jako "procedury":

  • procedury;
  • funkcje;
  • funkcje triggerow;
  • funkcje agregujące;
  • funkcje okien

Kod źródłowy tych obiektów jest przechowywany w tabeli pg_proc katalogu systemowego. Procedury, które pojawiły się wraz z PostgreSQL 11, są bardzo podobne do funkcji. Główne różnice między nimi to:

  • Funkcje muszą deklarować argumenty wyjściowe (argumenty RETURNS lub OUT). Mogą zwracać dowolny typ danych lub zestawy wierszy. Możliwe jest użycie void dla funkcji bez argumentu wyjściowego; była to metoda używana do emulacji zachowania procedury przed jej wprowadzeniem za pomocą PostgreSQL 11. Procedury nie posiadają kodu powrotu (można jednak użyć parametrów OUT lub INOUT).
  • Procedury zapewniają wsparcie kontroli transakcyjnej, czyli możliwość zatwierdzenia lub wycofania zmian dokonanych do tego momentu przez procedurę. Cała funkcja jest wykonywana w transakcji wywołującej.
  • Procedury są wywoływane wyłącznie przez komendę SQL CALL; funkcje mogą być wywoływane w większości kolejności DML/DQL (w tym SELECT), ale nie przez CALL.
  • Funkcje mogą być deklarowane w taki sposób, że mogą być używane w określonych rolach (funkcja wyzwalacza, agregująca lub okienkowa)