Een functie maken en gebruiken in Azure Database for PostgreSQL

  • 5 minuten

We hebben al geleerd dat PostgreSQL verschillende talen ondersteunt. Functies kunnen worden onderverdeeld in vier verschillende typen:

  • Functies die zijn geschreven in SQL.
  • Procedurele taalfuncties, geschreven in een ondersteunde procedurele taal, zoals PL.pgSQL.
  • Interne functies.
  • C-taalfuncties.

Daarnaast kan het doel van de functie ook worden gecategoriseerd als vluchtig, onveranderbaar of stabiel.

Een vluchtige functie (de standaardfunctie) kan de database wijzigen en retourneert mogelijk niet noodzakelijkerwijs hetzelfde resultaat met dezelfde invoerparameters telkens. Dus elke keer dat deze functie wordt aangeroepen, moet deze opnieuw worden geëvalueerd.

Een stabiele functie kan de database niet wijzigen en retourneert hetzelfde resultaat als dezelfde argumenten worden doorgegeven en binnen dezelfde instructie worden uitgevoerd. Als deze functie meerdere keren wordt aangeroepen, kan de queryoptimalisatie de resultaten gebruiken van de laatste keer dat deze is aangeroepen.

Een onveranderbare functie kan de database niet wijzigen en retourneert dezelfde resultaten als dezelfde argumenten worden doorgegeven, ongeacht de query die deze aanroept.

De volatiliteit van een functie maakt een groot verschil met de efficiëntie waarmee de queryoptimalisatie deze verwerkt.

Een functie maken

Een functie retourneert één waarde en kan worden gebruikt in een SELECT-instructie.

De syntaxis voor het maken van een functie is:

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

Net als bij opgeslagen procedures wordt het symbool $$ gebruikt om de tekenreeks te starten en te beëindigen.

Functies hebben de volgende parameters:

  • name - eventueel de schemanaam opnemen.
  • argmode : de modus van het argument. Kan IN, OUT, INOUT of VARIADIC zijn. De standaardwaarde is IN. VARDIAC is een niet-gedefinieerd aantal invoerargumenten van hetzelfde type en wordt gevolgd door OUT-argumenten. OUT- en INOUT-argumenten kunnen niet worden gebruikt in combinatie met de notaties RETURNS TABLE.
  • argname - argumentnaam.
  • argtype : het gegevenstype argument. Dit kunnen basis-, samengestelde of domeintypen zijn of verwijzen naar een tabelkolomtype. Het kolomtype wordt geschreven als table_name.column_name%TYPE. Dit gegevenstype kan helpen bij het maken van een functie die onafhankelijk is van wijzigingen in de tabeldefinitie.
  • t_expr : een standaardwaarde (van hetzelfde type) als de parameter niet is opgegeven. Alleen IN- en INOUT-parameters hebben een standaardwaarde. Invoerparameters na een parameter met een standaardwaarde moeten ook standaardwaarden hebben.
  • opnieuw typen : het retourgegevenstype, dat een basis-, samengestelde of domeintype kan zijn of verwijzen naar een tabelkolomtype. Als de functie geen waarde retourneert, geeft u het retourtype op als ongeldig. Wanneer er OUT- of INOUT-parameters zijn, kan de COMPONENT RETURNS worden weggelaten. Indien aanwezig, moet het overeenkomen met het resultaattype dat wordt geïmpliceerd door de uitvoerparameters: RECORD als er meerdere uitvoerparameters zijn of hetzelfde type als de parameter voor één uitvoer. De SETOF-wijzigingsfunctie geeft aan dat de functie een set items retourneert in plaats van één item. Er wordt naar het type kolom verwezen door table_name te schrijven.
  • column_name : de naam van een uitvoerkolom in de syntaxis RETURNS TABLE. Deze parameter declareert een benoemde OUT-parameter, behalve dat RETURNS TABLE ook RETURNS SETOF impliceert.
  • column_type : het gegevenstype van een uitvoerkolom in de syntaxis RETURNS TABLE.
  • lang_name: de taal die wordt gebruikt om de procedure te schrijven. De standaardwaarde is sql als sql_body is opgegeven. Kan sql, c, intern of de naam zijn van een door de gebruiker gedefinieerde procedurele taal, bijvoorbeeld plpgsql.

Gebruik de trefwoorden ONVERANDERBAAR, STABLE of VOLATILE als hint voor de queryoptimalisatie over de functie. VOLATILE is de standaardinstelling.

Een functie aanroepen

Een functie kan in een query worden gebruikt door alle relevante parameters eraan door te geven. Voorbeeld:

SELECT myfunction(3), CatID, CatName
    FROM myCats

Ingebouwde functies

PostgreSQL bevat veel ingebouwde functies die u in uw query's kunt gebruiken. Deze onderwerpen hebben betrekking op het maken van vergelijkingen, het samenvoegen van gegevens, wiskundige functies, enzovoort. Zie de onlinedocumentatie voor een volledige lijst met PostgreSQL-functies.

Een voorbeeld van een ingebouwde tekenreeksfunctie is subtekenreeks.

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

Deze functie heeft drie invoerparameters:

  • Tekenreeks (type tekst)
  • VANAF begin (type geheel getal)
  • FOR count (type geheel getal)

Subtekenreeks retourneert een deel van de invoertekst, beginnend bij het beginteken en stoppen na het tellen van tekens. Voorbeeld:

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

Deze functie is hetzelfde als de subtekenreeks:

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

Notitie

Als u bekend bent met functies, zult u merken dat in de eerste versie sleutelwoorden worden gebruikt in plaats van komma's om argumenten te scheiden. PostgreSQL biedt beide versies van deze functies.