PL/pgSQL-funktioiden perusteet
Kirjoita PL/pgSQL-funktioita, joissa on parametreja, RETURNS TABLE, ohjausrakenteita (IF, LOOP, FOREACH) ja poikkeusten käsittelyä.
PL/pgSQL-funktioiden perusteet on ilmainen SQL Academy-oppitunti CoddyKitissä. Tämä on oppitunti 2/4. Voit lukea koko oppitunnin alta ilmaiseksi ja harjoitella sen jälkeen käytännössä selaimessa sisäänrakennetulla koodieditorilla ja ympäri vuorokauden käytettävissä olevan tekoälytuutorin avulla. Oppitunti kuuluu SQL Academy-oppimispolkuun, ja edistymisesi synkronoituu verkon ja CoddyKit-sovelluksen välillä. SQL Academy-kurssilla on yhteensä 4 oppituntia.
Mikä PL/pgSQL on?
PostgreSQL:n sisäänrakennettu proseduraalinen kieli. SQL:ää on laajennettu muuttujilla, ohjausrakenteilla, poikkeuksilla ja mahdollisuudella kutsua kyselyitä dynaamisesti. Kieltä käytetään tallennettuihin proseduureihin ja triggerifunktioihin.
Funktion runko
Yksinkertainen funktio:
CREATE OR REPLACE FUNCTION add(a INT, b INT)
RETURNS INT AS $$
BEGIN
RETURN a + b;
END;
$$ LANGUAGE plpgsql IMMUTABLE;
SELECT add(2, 3); -- 5Muuttujat
Ilmoittakaa muuttujat DECLARE-komennolla ja sijoittakaa arvot käyttämällä :=- tai INTO-lausetta:
CREATE FUNCTION user_orders(uid BIGINT)
RETURNS INT AS $$
DECLARE
cnt INT;
BEGIN
SELECT COUNT(*) INTO cnt FROM orders WHERE user_id = uid;
RETURN cnt;
END;
$$ LANGUAGE plpgsql STABLE;Ohjausrakenteet
IF / ELSE, FOR, WHILE, CASE:
IF cnt = 0 THEN
RAISE NOTICE 'no orders';
ELSIF cnt < 10 THEN
RAISE NOTICE 'few';
ELSE
RAISE NOTICE 'many';
END IF;
FOR i IN 1..10 LOOP
RAISE NOTICE '%', i;
END LOOP;Kyselyn tulosten läpikäynti
Kursorityylinen iteraatio:
CREATE FUNCTION recalc_totals() RETURNS VOID AS $$
DECLARE
r RECORD;
BEGIN
FOR r IN SELECT id FROM users LOOP
UPDATE users SET
order_count = (SELECT COUNT(*) FROM orders WHERE user_id = r.id)
WHERE id = r.id;
END LOOP;
END;
$$ LANGUAGE plpgsql;RETURNS TABLE
Palauttakaa tulosjoukko:
CREATE FUNCTION top_buyers(n INT)
RETURNS TABLE (user_id BIGINT, total NUMERIC) AS $$
BEGIN
RETURN QUERY
SELECT o.user_id, SUM(o.total)
FROM orders o
GROUP BY o.user_id
ORDER BY SUM(o.total) DESC
LIMIT n;
END;
$$ LANGUAGE plpgsql STABLE;
SELECT * FROM top_buyers(10);IN / OUT / INOUT -parametrit
Useita palautettavia arvoja:
CREATE FUNCTION stats(uid BIGINT,
OUT total NUMERIC,
OUT cnt INT) AS $$
BEGIN
SELECT SUM(total), COUNT(*) INTO total, cnt
FROM orders WHERE user_id = uid;
END;
$$ LANGUAGE plpgsql STABLE;
SELECT * FROM stats(42); -- total | cntPoikkeusten käsittely
BEGIN ... EXCEPTION WHEN ... THEN:
BEGIN
INSERT INTO users (email) VALUES ($1);
EXCEPTION
WHEN unique_violation THEN
RAISE NOTICE 'email already exists';
WHEN OTHERS THEN
RAISE;
END;RAISE lokikirjausta varten
Tasot: DEBUG, LOG, INFO, NOTICE, WARNING, EXCEPTION:
RAISE NOTICE 'processed % rows', cnt;
RAISE EXCEPTION 'bad state: %', state USING ERRCODE = 'check_violation';Volatiliteettimääritteet
Merkitkää funktiot suunnittelijaa varten oikein:
- IMMUTABLE — samat argumentit tuottavat aina saman tuloksen (ei I/O-toimintoja); mahdollistaa upotuksen ja indeksien hyödyntämisen
- STABLE — samat argumentit tuottavat saman tuloksen kyselyn aikana
- VOLATILE — tulos voi vaihdella kutsujen välillä (oletus; oikea esimerkiksi NOW()- ja RANDOM()-funktioille)
SECURITY DEFINER
Suoritetaan funktion omistajan oikeuksilla — käyttäkää tätä varoen:
CREATE FUNCTION sensitive_op() RETURNS VOID
LANGUAGE plpgsql
SECURITY DEFINER
AS $$ ... $$;
-- Always set search_path explicitly to avoid hijacking:
ALTER FUNCTION sensitive_op() SET search_path = public, pg_temp;Kertaus
PL/pgSQL laajentaa SQL:ää silmukoilla, IF-lauseilla, poikkeuksilla ja rakenteisilla palautusarvoilla. Käyttäkää sitä yhtenäiseen monivaiheiseen logiikkaan, jonka suorittaminen lähellä dataa on hyödyllistä.
Pikatarkistus
Mitä volatiliteettimerkintää funktion tulisi käyttää, jos se palauttaa samoilla argumenteilla aina saman tuloksen eikä sillä ole sivuvaikutuksia tai I/O-operaatioita?
Opi SQL tekoälytuutorin avulla — ilmaiseksi
Kirjoita ja suorita oikeaa koodia selaimessa, saa välitöntä apua tekoälytuutorilta ympäri vuorokauden ja jatka siitä, mihin jäit, verkossa tai sovelluksessa.
- Kurssit
- 46
- Oppitunnit
- 183
Usein kysytyt kysymykset
Onko oppitunti ”PL/pgSQL-funktioiden perusteet” ilmainen?
Kyllä – oppitunnin ”PL/pgSQL-funktioiden perusteet” koko tekstin voi lukea täällä verkossa ilmaiseksi. Jos haluat harjoitella interaktiivisesti sisäänrakennetulla koodieditorilla ja ympäri vuorokauden käytettävissä olevan tekoälytuutorin avulla sekä avata koko SQL Academy-kurssin, päivitä CoddyKit PROhon. SQL Academy-kurssilla on yhteensä 4 oppituntia.
Mitä opin oppitunnilla ”PL/pgSQL-funktioiden perusteet”?
Kirjoita PL/pgSQL-funktioita, joissa on parametreja, RETURNS TABLE, ohjausrakenteita (IF, LOOP, FOREACH) ja poikkeusten käsittelyä. Harjoittelet SQL Academy-aihetta koodilla, jonka suoritat suoraan selaimessa. Ympäri vuorokauden käytettävissä oleva tekoälytuutori vastaa kysymyksiisi oppitunnin aikana.
Tarvitsenko kokemusta aloittaakseni SQL Academy-opiskelun?
Aiempi kokemus ei ole tarpeen. CoddyKitin SQL Academy-oppimispolku sopii vasta-alkajista edistyneisiin, joten voit aloittaa tästä tai alusta ja edetä omaan tahtiisi. Tämä on oppitunti 2/4.
Kuinka kauan ”PL/pgSQL-funktioiden perusteet”-oppitunnin suorittaminen kestää?
Useimmat CoddyKitin oppitunnit kestävät noin 5–10 minuuttia. Jokainen oppitunti on lyhyt ja interaktiivinen, joten edistyt tasaisesti ja voit jatkaa siitä, mihin jäit – sekä verkossa että sovelluksessa.
Voinko kirjoittaa ja suorittaa koodia tällä SQL Academy-oppitunnilla?
Kyllä. Jokainen SQL Academy-oppitunti sisältää sisäänrakennetun koodieditorin, joten voit kirjoittaa ja suorittaa oikeaa koodia suoraan selaimessa ja saada välitöntä palautetta tekoälyltä – paikallista asennusta ei tarvita.
Kaikki tämän kurssin oppitunnit
- Triggerin rakenne: BEFORE/AFTER, FOR EACH ROW
- PL/pgSQL-funktioiden perusteet
- DO-lohkot ja anonyymi koodi
- Taulujen auditointi triggereillä