Bài học theo mô-đun (2/4)
Logic nâng cao với PL/pgSQL
Functions written in LANGUAGE sql can only run standard queries one after another. If we needed something more advanced like "IF the entered total is greater than 100 THEN apply a discount", plain bare SQL can't do that on its own. We need to use PL/pgSQL, a full-fledged procedural programming language built into Postgres.
In PL/pgSQL we add the classic bodies made of BEGIN .. END, variables, and conditional statements.
CREATE OR REPLACE FUNCTION valuta_cliente(ordini INTEGER)
RETURNS TEXT AS $$
BEGIN
IF ordini > 10 THEN
RETURN 'VIP';
ELSE
RETURN 'Standard';
END IF;
END;
$$ LANGUAGE plpgsql;This power lets us offload massive blocks of repetitive logic we would otherwise write in JS, packing them into safe, fast functions executed close to the data with very low latency!
Try it yourself
We want a complex function that replaces itself if it already exists ('get_price_label'), accepts 'price' as NUMERIC, and returns a verbose TEXT.\nUse PL/pgSQL. Open BEGIN.\nWrite the logic: if price is > 100.00 return the string 'Premium'. ELSE return 'Economico'.\nClose the conditional END; and the body.
Hiển thị gợi ý
CREATE OR REPLACE FUNCTION get_price_label(price NUMERIC) RETURNS TEXT AS $$ BEGIN IF price > 100.00 THEN RETURN 'Premium'; ELSE RETURN 'Economico'; END IF; END; $$ LANGUAGE plpgsql;
Giải pháp khả dụng sau 3 lần thử
You can declare temporary variables before BEGIN with the DECLARE keyword.\nWrite a function 'saluta_utente(userid INTEGER)' -> Returns TEXT, PL/pgSQL logic.\nInside it DECLARE a string variable 'nome_utente TEXT;'.\nThen, inside BEGIN, populate that variable with the first_name by dynamically querying 'customers' using the special clause 'SELECT first_name INTO nome_utente FROM... WHERE id = userid'. Finally, RETURN concatenates the variable to 'Ciao '.
Hiển thị gợi ý
DECLARE nome_utente TEXT; \nBEGIN SELECT first_name INTO nome_utente... \nRETURN 'Ciao ' || nome_utente;
Giải pháp khả dụng sau 3 lần thử