Skapa och använda en funktion i Azure Database for PostgreSQL

  • 5 minuter

Vi har redan lärt oss att PostgreSQL stöder olika språk. Funktioner kan kategoriseras i fyra olika typer:

  • Funktioner skrivna i SQL.
  • Processuella språkfunktioner, skrivna på ett förfarandespråk som stöds, till exempel PL.pgSQL.
  • Interna funktioner.
  • C-språkfunktioner.

Dessutom kan funktionens syfte även kategoriseras som flyktigt, oföränderligt eller stabilt.

En flyktig funktion (standardfunktionen) kan ändra databasen och kanske inte nödvändigtvis returnerar samma resultat med samma indataparametrar varje gång. Så varje gång den här funktionen anropas måste den omvärderas.

En stabil funktion kan inte ändra databasen och returnerar samma resultat om samma argument skickas och körs inom samma instruktion. Om den här funktionen anropas flera gånger kan frågeoptimeraren använda resultaten från den senaste gången den anropades.

En oföränderlig funktion kan inte ändra databasen och returnerar samma resultat om samma argument skickas, oavsett vilken fråga som anropar den.

Volatiliteten i en funktion gör stor skillnad för den effektivitet som frågeoptimeraren hanterar den med.

Skapa en funktion

En funktion returnerar ett enda värde och kan användas i en SELECT-instruktion.

Syntaxen för att skapa en funktion är:

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

Precis som med lagrade procedurer används $$ -symbolen för att starta och avsluta strängen.

Funktionerna har följande parametrar:

  • name – om du vill kan du inkludera schemanamnet.
  • argmode – argumentets läge. Kan vara IN, OUT, INOUT eller VARIADIC. Standardvärdet är IN. VARDIAC är ett odefinierat antal indataargument av samma typ och följs av OUT-argument. OUT- och INOUT-argument kan inte användas tillsammans med RETURNERAR TABELLnotationer.
  • argname – argumentnamn.
  • argtype – argumentets datatyp. Kan vara bas-, sammansatta eller domäntyper eller referera till en tabellkolumntyp. Kolumntypen skrivs som table_name.column_name%TYPE. Den här datatypen kan hjälpa dig att göra en funktion oberoende av ändringar i tabelldefinitionen.
  • t_expr – Ett standardvärde (av samma typ) om parametern inte har angetts. Endast IN- och INOUT-parametrar har ett standardvärde. Indataparametrar som följer en parameter med ett standardvärde måste också ha standardvärden.
  • rettype – returdatatypen, som kan vara en bas,sammansatt eller domäntyp, eller referera till en tabellkolumntyp. Om funktionen inte returnerar ett värde anger du returtypen som void. När det finns OUT- eller INOUT-parametrar kan RETURNS-satsen utelämnas. Om den finns måste den överensstämma med den resultattyp som anges av utdataparametrarna: RECORD om det finns flera utdataparametrar eller samma typ som den enskilda utdataparametern. SETOF-modifieraren anger att funktionen returnerar en uppsättning objekt i stället för ett enda objekt. Typen av kolumn refereras genom att skriva table_name.
  • column_name – Namnet på en utdatakolumn i SYNTAXEN FÖR RETURTABELL. Den här parametern deklarerar en namngiven OUT-parameter, förutom att RETURNS TABLE även innebär RETURNS SETOF.
  • column_type – datatypen för en utdatakolumn i syntaxen FÖR RETURTABELL.
  • lang_name – det språk som används för att skriva proceduren. Standardvärdet är sql om sql_body anges. Kan vara sql, c, internt eller namnet på ett användardefinierat procedurspråk, till exempel plpgsql.

Använd nyckelorden IMMUTABLE, STABLE eller VOLATILE som ett tips till frågeoptimeraren om funktionen. VOLATILE är standardvärdet.

Anropa en funktion

En funktion kan användas i en fråga genom att skicka relevanta parametrar till den. Till exempel:

SELECT myfunction(3), CatID, CatName
    FROM myCats

Inbyggda funktioner

PostgreSQL innehåller många inbyggda funktioner som du kan använda i dina frågor. Dessa omfattar att göra jämförelser, aggregera data, matematiska funktioner osv. I onlinedokumentationen finns en fullständig lista över PostgreSQL-funktioner.

Ett exempel på en inbyggd strängfunktion är delsträng.

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

Den här funktionen tar tre indataparametrar:

  • Sträng (skriv text)
  • FROM start (typ heltal)
  • ANTAL (typ heltal)

Delsträngen returnerar en del av indatatexten med början vid starttecknet och stoppas efter antal tecken. Till exempel:

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

Den här funktionen är samma som delsträngen:

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

Kommentar

Om du är bekant med funktioner har du märkt att den första versionen använder nyckelord i stället för kommatecken för att separera argument. PostgreSQL innehåller båda versionerna av dessa funktioner.