Hvordan lage PostgreSQL brukerdefinert funksjon

“Når det krever å utføre mange SQL-setninger flere ganger, er det bedre å skrive en brukerdefinert PostgreSQL-funksjon eller prosedyre med disse SQL-setningene. Den fjerner duplikatkoden og gjenbruker koden ved å ringe funksjonen fra forskjellige applikasjoner. Måten å lage og bruke forskjellige typer PostgreSQL brukerdefinerte funksjoner er vist i denne opplæringen.”

Syntaks

Syntaksen til PostgreSQL -funksjonen er gitt nedenfor.

Opprett eller erstatt funksjonsfunksjon_navn (argument_list)
Returnerer return_type som $ variabel_name $
ERKLÆRE
erklæring;
[…]
BEGYNNE

[…]
Return variabel_name | verdi
SLUTT;
Språk PLPGSQL;

• Opprett nøkkelord brukes til å lage en ny brukerdefinert funksjon, og erstatte nøkkelord brukes til å endre den eksisterende funksjonen.
• Navnet på funksjonen er definert av funksjonsnavnet, og funksjonen kan ta ett eller flere argumenter. Tre typer argumenter kan brukes i funksjonen. Disse er I, UTE, og Inout. Standardargumentet til funksjonen er I argument. Den brukes til å gi verdien inn i funksjonen, og den kan ikke endres inne i funksjonen. De UTE Argument brukes til å returnere verdien fra funksjonen. De Inout Argument kan brukes til å gi verdi til funksjonen og returverdien fra funksjonen. Det kan tilordnes i funksjonen som ut argument.
• Datatypen for variabelen som vil bli returnert av funksjonen er nevnt etter Returns -nøkkelordet.
• Funksjonens kropp vil starte med deklaret nøkkelord, der de nødvendige variablene vil bli erklært. Deretter vil de nødvendige SQL -utsagnene bli definert i start- og sluttblokkene.
• Navnet på prosedyrespråket er definert etter språket nøkkelordet som funksjonen skal utføres.

Forutsetninger

EN. Installer PostgreSQL

Du må installere den nyeste versjonen av PostgreSQL -pakker på Linux -operativsystemet før du utfører SQL -uttalelsene som vises i denne opplæringen. Kjør følgende kommandoer for å installere og starte PostgreSQL:

$ sudo apt-get -y installer postgreSql postgreSql-contrib
$ sudo SystemCTL Start PostgreSql.service

Kjør følgende kommando for å logge inn på PostgreSQL med rotprivilegium.

$ sudo -u postgres psql

B. Du kan opprette en database som heter “Testdb” Ved å kjøre følgende SQL -setning.

# Opprett databasetestdb;

Hvis du vil lage et bord i Testdb database, så må du skrive “\ C” å endre databasen. Men i denne opplæringen endret jeg ikke databasen og tabellen, og alle funksjoner ble opprettet i standarddatabasen som heter Postgres.

C. Lag en tabell som heter produkter med fire felt og sett inn fire poster i tabellen for å teste de brukerdefinerte funksjonene som vil bli opprettet senere i denne opplæringen.

# Lag bordprodukter (
ID Seriell primærnøkkel,
Navn varchar (30),
Type Varchar (20),
pris penger);

D. Sett inn fire poster i tabellen.

# Sett inn i produkter (navn, type, pris)
Verdier ('Samsung A40', 'Mobile', 300),
('A4', 'Mus', 20),
('Sony 42' ',' TV ', 1000),
('Samsung 3TB', 'HDD', 600);

Ulike eksempler på PostgreSQL brukerdefinerte funksjoner

Bruken av brukerdefinerte funksjoner med argument og uten argument i PostgreSQL er vist nedenfor ved å bruke flere eksempler.

Eksempel-1: Brukerdefinert funksjon uten argument og returverdi

Kjør følgende SQL -setning for å opprette en funksjon som er navngitt funksjon1 () uten noe argument, og funksjonen vil ikke returnere ingenting. Funksjonen vil oppdatere posten til produkttabellen der ID -verdien er 3 etter utførelse.

# Opprett eller erstatt funksjonsfunksjon1 ()
Returnerer tomrom som
$$
BEGYNNE
--Oppdater prisverdien på den tredje posten
Oppdater produkter sett pris = 1500 der ID = 3;
SLUTT;
$$
Språk 'PLPGSQL';

Følgende utgang vises hvis funksjonen opprettes med suksess i Postgres database.

Kjør følgende SQL -uttalelse for å lese alle poster av Produkter Tabell før du utfører funksjon1 ().

# Velg * fra produkter;

Følgende utgang viser de innsatte postene til Produkter bord.

Kjør følgende SQL -setning for å utføre funksjonen.

# Velg funksjon1 ();

Følgende utgang viser at en rad påvirkes av å kalle funksjonen funksjon1 (). Verdien på prisfeltet på tabellen vil bli oppdatert der ID -verdien er 3.

Kjør følgende SQL -uttalelse for å lese alle poster av Produkter Tabell etter utførelse funksjon1 ().

# Velg * fra produkter;

Følgende utdata viser at prisverdien på produkttabellen er oppdatert der ID -verdien er 3, og den er 1500 nå.

Eksempel-2: Brukerdefinert funksjon med i argumentasjonen

Standardargumentet til funksjonen er i argumentasjon. Kjør følgende SQL -setning for å opprette en funksjon som er navngitt funksjon2 () med to argumenter, og funksjonen vil ikke returnere ingenting. Funksjonen vil sette inn en ny post til Produkter Tabell med argumentverdiene.

# Opprett eller erstatt funksjonsfunksjon2 (navn varchar, type varchar, pris int)
Returnerer tomrom som
$$
BEGYNNE
--Sett inn en ny post
Sett inn produkter (navn, type, pris)
Verdier (navn, type, pris);
SLUTT;
$$
Språk 'PLPGSQL';

Følgende utgang vises hvis funksjonen opprettes med suksess i Postgres database.

Kjør følgende SQL -setning for å utføre funksjonen tre i argumentverdier. Dette er "iPhone", "mobil" og 2000.

# Velg Function2 ('iPhone', 'Mobile', 2000);

Følgende utgang viser at en rad påvirkes av å kalle funksjonen funksjon2 (), og en ny post vil bli satt inn i tabellen.

Kjør følgende SQL -uttalelse for å lese alle poster av Produkter Tabell etter utførelse funksjon2 ().

# Velg * fra produkter;

Følgende utgang viser at en ny post er satt inn i Produkter bord.

Eksempel-3: Brukerdefinert funksjon uten argument

UT -argumentet brukes til å returnere verdien fra funksjonen. Kjør følgende SQL -setning for å opprette en funksjon som er navngitt funksjon3 () med ett argument av pengetype, og funksjonen vil returnere data om pengetype. Funksjonen vil returnere den tildelte verdien av OUT -argumentet.

# Opprett eller erstatt funksjonsfunksjon3 (Pris ut penger)
Returnerer penger som
$$
BEGYNNE
--Tilordne en verdi til argumentet
Pris: = 1000;
SLUTT;
$$
Språk 'PLPGSQL';

Følgende utgang vises hvis funksjonen opprettes med suksess i Postgres database.

Kjør følgende SQL -setning for å slette poster fra produkttabellen basert på verdien returnert av Function3 ().

# Slett fra produkter der pris <= function3();

Følgende utgang viser at tre rader påvirkes av å kalle funksjonen funksjon3 (). Den returnerte verdien av funksjonen er 1000, som samsvarer med de tre postene til Produkter bord der id verdier er 1, 2, og 4.

Kjør følgende SQL -uttalelse for å lese alle poster av Produkter Tabell etter utførelse funksjon3 (). Utgangen viser at det finnes to poster i tabellen der pris Verdiene er mer enn 1000.

# Velg * fra produkter;

Følgende utgang viser at 3 poster er blitt slettet fra Produkter bord.

Eksempel-4: Brukerdefinert funksjon med inout-argument

Det indre argumentet brukes i funksjonen for å gi verdier i funksjonen og returnere verdiene fra funksjonen. Kjør følgende SQL -setning for å opprette en funksjon som er navngitt regne ut() med et inout -argument om float -type og en i argumentasjon av heltallstype. Funksjonen vil returnere data for float -type. Funksjonen vil bli utført med salgsbeløpet, som vil være en flyteverdi, og rabatten, som vil være et heltall. Salgsbeløpet etter rabatten vil bli beregnet basert på i argumentverdiene. Deretter vil det beregnede salgsbeløpet bli returnert fra funksjonen ved å bruke OUT -argumentet.

# Opprett eller erstatt funksjon Beregn (
Sales_amount Inout Float, rabatt int)
Returnerer flyte som
$$
BEGYNNE
--Beregn rabattbeløpet
Sales_amount: = Sales_amount - (Sales_amount*rabatt/100);
SLUTT;
$$
Språk 'PLPGSQL';

Følgende utgang vises hvis funksjonen opprettes med suksess i Postgres database.

Kjør følgende uttalelse for å utføre funksjonen med 10000 som salgsbeløp og 5 som rabattbeløp

#Selge beregne (10000, 5) som rabatt_amount;

5% av 10000 er 500 og 10000-500 = 9500. Følgende produksjon viser salgsbeløp etter 5% rabatt.

Eksempel-5: Bruker-Defined Funksjon med de Betinget uttalelse

Kjør følgende SQL -setning for å opprette en funksjon som er navngitt search_product () med et argument, og funksjonen vil returnere dataene fra varchar -type. Måten å erklære funksjonsvariabelen og bruke den betingede setningen er vist i denne funksjonen. Funksjonsvariabelen som heter Produktnavn har blitt definert med Navn type av Produkter bord. Verdien av i argumentasjonen, tp, vil bli matchet med verdien av type felt av Produkter bord. Hvis det ikke blir funnet noen kamp i tabellen, vil en feil bli reist med meldingen, "Produkttype eksisterer ikke". Hvis noen kamp blir funnet, så er verdien av Navn Feltet av den produkttypen vil bli lagret i funksjonsvariabelen, og variabelen vil bli returnert fra funksjonen.

# Opprett eller erstatt funksjonssøk_produkt (TP VARCHAR)
Returnerer Varchar som
$$
ERKLÆRE
produktnavn produkter.Navn%type;
BEGYNNE
-- Søk på produktnavnet basert på typen
Velg navn i produktnavn
Fra produkter
Hvor produkter.Type = tp;
Hvis ikke funnet, så
Heving 'produkttype eksisterer ikke.';
SLUTT OM;
Return produktnavn;
SLUTT;
$$
Språk 'PLPGSQL';

Følgende utgang vises hvis funksjonen opprettes med suksess i Postgres database.

Kjør følgende SQL -setning for å lese alle poster fra produkttabellen.

# Velg * fra produkter;

Kjør følgende SQL -setning for å utføre funksjonen Search_Product () med produkttypen “Mobil”.

# Velg search_product ('mobil');

Kjør følgende SQL -setning for å utføre funksjonen Search_Product () med produkttypen “Ac”.

# Velg search_product ('ac');

Følgende utgang viser at Produkter Tabellen inneholder to poster over typeverdier, "TV”Og“Mobil”. Utgangen fra den første utførelsen av funksjonen har returnert “Iphon” fordi produkttypen “mobil” eksisterer i tabellen. Utgangen fra den andre utførelsen av funksjonen har returnert en feilmelding fordi produkttypen “AC” ikke eksisterer i tabellen.

Eksempel-6: Brukerdefinert funksjon med aggregatfunksjon

Enhver aggregatfunksjon som støttes av SQL, kan brukes i PostgreSQL -funksjonen. Kjør følgende SQL -setning for å opprette en funksjon som er navngitt count_product () uten noe argument, og funksjonen vil returnere dataene fra heltallstype. Telling () aggregatfunksjon har blitt brukt i funksjonen som brukes til å telle antall rader basert på den spesifikke tilstanden. Her er en funksjonsvariabel som heter Total_Product blitt erklært i funksjonen for å lagre returverdien til Count () -funksjonen. Count () -funksjonen har blitt brukt i SELECT -spørringen for å telle de postene der produktnavnet starter med ordet “Sony”.

# Opprett eller erstatt funksjon count_product ()
Returnerer int som
$$
ERKLÆRE
total_produkt int;
BEGYNNE
--Telle det totale antallet av det aktuelle produktet
Velg Count (*) til Total_Product fra produkter
Hvor navn som 'Sony%';
Return total_product;
SLUTT;
$$
Språk PLPGSQL;

Følgende utgang vises hvis funksjonen opprettes med suksess i Postgres database.

Kjør følgende kommando for å utføre funksjonen, count_product ().

# Velg count_product ();

Følgende utgang viser at telling () -funksjonen returnerer verdien 1 fordi det bare er en post som samsvarer med verdien, “Sony”.

Konklusjon

Hensikten med å bruke PostgreSQL -funksjoner og forskjellige måter å bruke PostgreSQL -funksjoner med In, Out og Inout -argumenter er beskrevet i denne opplæringen ved å lage flere PostgreSQL -funksjoner. In -argumentet brukes til å ta verdi inn i funksjonen. UT -argumentet brukes til å returnere verdien fra funksjonen. Det indre argumentet brukes til å ta inngangsverdi i funksjonen og returnere utgangen fra funksjonen. Funksjonen kan også opprettes uten noe argument. Jeg håper brukeren vil kunne opprette og bruke PostgreSQL -funksjonen ordentlig etter å ha lest denne opplæringen.