Tworzenie i używanie funkcji w usłudze Azure Database for PostgreSQL

  • 5 min

Dowiedzieliśmy się już, że usługa PostgreSQL obsługuje różne języki. Funkcje można podzielić na cztery różne typy:

  • Funkcje napisane w języku SQL.
  • Funkcje języka proceduralnego napisane w obsługiwanym języku proceduralnym, takim jak PL.pgSQL.
  • Funkcje wewnętrzne.
  • Funkcje języka C.

Ponadto przeznaczenie funkcji można również podzielić na nietrwałe, niezmienne lub stabilne.

Funkcja volatile (domyślna) może modyfikować bazę danych i może niekoniecznie zwracać ten sam wynik z tymi samymi parametrami wejściowymi za każdym razem. Dlatego za każdym razem, gdy ta funkcja jest wywoływana, musi zostać ponownie zwalczona.

Funkcja stabilna nie może zmodyfikować bazy danych i zwraca ten sam wynik, jeśli przekazano te same argumenty i uruchomiono w ramach tej samej instrukcji. Jeśli ta funkcja jest wywoływana wiele razy, optymalizator zapytań może użyć wyników z ostatniego wywołania.

Funkcja niezmienna nie może modyfikować bazy danych i zwraca te same wyniki, jeśli przekazano te same argumenty, niezależnie od zapytania, które go wywołuje.

Zmienność funkcji ma dużą różnicę w wydajności, za pomocą której optymalizator zapytań go obsługuje.

Tworzenie funkcji

Funkcja zwraca pojedynczą wartość i może być używana w instrukcji SELECT.

Składnia tworzenia funkcji to:

CREATE [OR REPLACE] FUNCTION
myfunction ([inputparam] type {default})
RETURNS returntype AS
$$
SQL body
$$
LANGUAGE 'language_name';
CREATE FUNCTION

Podobnie jak w przypadku procedur składowanych, symbol $$ jest używany do uruchamiania i kończenia ciągu.

Funkcje przyjmują następujące parametry:

  • name — opcjonalnie dołącz nazwę schematu.
  • argmode — tryb argumentu. Może być IN, OUT, INOUT lub VARIADIC. Wartość domyślna to IN. VARDIAC jest niezdefiniowaną liczbą argumentów wejściowych tego samego typu i następuje argumenty OUT. Argumenty OUT i INOUT nie mogą być używane razem z notacjami TABELI RETURNS.
  • argname — nazwa argumentu.
  • argtype — typ danych argumentu. Może to być typ kolumny podstawowej, złożonej lub domeny albo odwołanie do typu kolumny tabeli. Typ kolumny jest zapisywany jako table_name.nazwa_kolumny%TYPE. Ten typ danych może pomóc w wprowadzeniu funkcji niezależnie od zmian definicji tabeli.
  • t_expr — wartość domyślna (tego samego typu), jeśli parametr nie jest określony. Tylko parametry IN i INOUT mają wartość domyślną. Parametry wejściowe po parametrze z wartością domyślną muszą również mieć wartości domyślne.
  • rettype — zwracany typ danych, który może być podstawowym, złożonym lub typem domeny albo odwołaniem do typu kolumny tabeli. Jeśli funkcja nie zwraca wartości, określ zwracany typ jako void. Jeśli istnieją parametry OUT lub INOUT, klauzula RETURNS może zostać pominięta. Jeśli istnieje, musi zgadzać się z typem wyniku dorozumianym przez parametry wyjściowe: RECORD, jeśli istnieje wiele parametrów wyjściowych lub tego samego typu co pojedynczy parametr wyjściowy. Modyfikator SETOF wskazuje, że funkcja zwraca zestaw elementów, a nie pojedynczy element. Do typu kolumny odwołuje się table_name.
  • column_name — nazwa kolumny wyjściowej w składni ZWRACA TABELĘ. Ten parametr deklaruje nazwany parametr OUT, z tą różnicą, że funkcja ZWRACA TABELĘ oznacza również FUNKCJA ZWRACA SETOF.
  • column_type — typ danych kolumny wyjściowej w składni ZWRACA TABELĘ.
  • lang_name — język używany do pisania procedury. Wartość domyślna to sql, jeśli określono sql_body. Może to być sql, c, internal lub nazwa języka proceduralnego zdefiniowanego przez użytkownika, na przykład plpgsql.

Użyj słów kluczowych IMMUTABLE, STABLE lub VOLATILE jako wskazówki dla optymalizatora zapytań na temat funkcji. Volatile jest wartością domyślną.

Wywoływanie funkcji

Funkcja może być używana w zapytaniu, przekazując do niej wszystkie odpowiednie parametry. Na przykład:

SELECT myfunction(3), CatID, CatName
    FROM myCats

Wbudowane funkcje

Usługa PostgreSQL zawiera wiele wbudowanych funkcji, których można używać w zapytaniach. Te zagadnienia obejmują porównywanie, agregowanie danych, funkcje matematyczne itp. Zobacz dokumentację online, aby uzyskać pełną listę funkcji PostgreSQL.

Przykładem wbudowanej funkcji ciągu jest podciąg.

substring (*string* text [ FROM *start* integer ] [ FOR *count* integer ] ) → text

Ta funkcja przyjmuje trzy parametry wejściowe:

  • Ciąg (wpisz tekst)
  • OD początku (wpisz liczbę całkowitą)
  • FOR count (typ integer)

Podciąg zwraca część tekstu wejściowego, zaczynając od znaku początkowego i zatrzymując się po zliczaniu znaków. Na przykład:

substring('Thomas' from 2 for 3) → hom
substring('Thomas' from 3) → omas
substring('Thomas' for 2) → Th

Ta funkcja jest taka sama jak podciąg:

substr ( *string* text, *start* integer [, *count* integer ] ) → text
substr('alphabet', 3) → phabet
substr('alphabet', 3, 2) → ph

Uwaga

Jeśli znasz funkcje, zauważysz, że pierwsza wersja używa słów kluczowych, a nie przecinków do oddzielania argumentów. Usługa PostgreSQL udostępnia obie wersje tych funkcji.