Opret og brug en funktion i Azure Database til PostgreSQL

Vi har allerede lært, at PostgreSQL understøtter forskellige sprog. Funktioner kan kategoriseres i fire forskellige typer:

• Funktioner, der er skrevet i SQL.
• Funktioner til proceduresprog, der er skrevet på et understøttet proceduresprog, f.eks. PL.pgSQL.
• Interne funktioner.
• C-sprogfunktioner.

Derudover kan formålet med funktionen også kategoriseres som flygtige, uforanderligeeller stabile.

En flygtige (standardfunktionen) kan ændre databasen og returnerer muligvis ikke nødvendigvis det samme resultat med de samme inputparametre hver gang. Så hver gang denne funktion kaldes, skal den evalueres igen.

En stabil funktion kan ikke ændre databasen og returnerer det samme resultat, hvis de samme argumenter overføres og køres i den samme sætning. Hvis denne funktion kaldes flere gange, kan forespørgselsoptimeringsprogrammet bruge resultaterne fra sidste gang, den blev kaldt.

En uforanderlige funktion kan ikke ændre databasen og returnerer de samme resultater, hvis de overføres med de samme argumenter, uanset hvilken forespørgsel der kalder den.

En funktions volatilitet gør en stor forskel for den effektivitet, som forespørgselsoptimeringsprogrammet håndterer den med.

Opret en funktion

En funktion returnerer en enkelt værdi og kan bruges i en SELECT-sætning.

Syntaksen for oprettelse af en funktion er:

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

Som med lagrede procedurer bruges symbolet $$ til at starte og afslutte strengen.

Funktioner bruger følgende parametre:

• navn – medtag eventuelt skemanavnet.
• argmode – argumentets tilstand. Kan være IN, OUT, INOUT eller VARIADIC. Standarden er IN. VARDIAC er et udefineret antal inputargumenter af samme type, og efterfølges af OUT-argumenter. ARGUMENTERNE OUT og INOUT kan ikke bruges sammen med RETURNS TABLE-notationerne.
• argname – argumentnavn.
• argtype – argumentdatatypen. Kan være grundlæggende, sammensatte eller domænetyper eller referere til en tabelkolonnetype. Kolonnetypen skrives som table_name.column_name%TYPE. Denne datatype kan hjælpe med at gøre en funktion uafhængig af ændringer i tabeldefinitionen.
• t_expr – En standardværdi (af samme type), hvis parameteren ikke er angivet. Kun PARAMETRENE IN og INOUT har en standardværdi. Inputparametre, der følger en parameter med en standardværdi, skal også have standardværdier.
• rettype – Returdatatypen, som kan være en grundlæggende, sammensat eller domænetype, eller som refererer til en tabelkolonnetype. Hvis funktionen ikke returnerer en værdi, skal du angive returtypen som void. Når der er OUT- eller INOUT-parametre, kan RETURNS-delsætningen udelades. Hvis den er til stede, skal den være enig i den resultattype, der er underforstået af outputparametrene: RECORD, hvis der er flere outputparametre, eller den samme type som parameteren for enkelt output. SETOF-modifikatoren angiver, at funktionen returnerer et sæt elementer i stedet for et enkelt element. Der refereres til typen af en kolonne ved at skrive table_name.
• column_name – Navnet på en outputkolonne i syntaksen RETURNS TABLE. Denne parameter deklarerer en navngivet OUT-parameter, bortset fra at RETURNS TABLE også indebærer RETURNS SETOF.
• column_type – Datatypen for en outputkolonne i syntaksen RETURNS TABLE.
• lang_name – det sprog, der blev brugt til at skrive proceduren. Standarden er SQL, hvis sql_body er angivet. Kan være sql, c, intern eller navnet på et brugerdefineret proceduresprog, f.eks. plpgsql.

Brug nøgleordene IMMUTABLE, STABLE eller VOLATILE som et tip til forespørgselsoptimeringsfunktionen om funktionen. VOLATILE er standard.

Kald en funktion

En funktion kan bruges i en forespørgsel ved at overføre relevante parametre til den. For eksempel:

SELECT myfunction(3), CatID, CatName
    FROM myCats

Indbyggede funktioner

PostgreSQL indeholder mange indbyggede funktioner, som du kan bruge i dine forespørgsler. Disse omfatter sammenligninger, sammenlægning af data, matematiske funktioner osv. Se onlinedokumentationen for at få en komplet liste over PostgreSQL-funktioner.

Et eksempel på en indbygget strengfunktion er understreng.

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

Denne funktion bruger tre inputparametre:

• Streng (skriv tekst)
• FRA start (skriv heltal)
• FOR antal (type heltal)

Understrengen returnerer en del af inputteksten med start ved starter tegn og stopper efter antal tegn. For eksempel:

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

Denne funktion er den samme som understrengen:

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