{{tag>Brouillon SQL Database}} # Notes Postgres PL/PGSQL Voir : * https://docs.postgresql.fr/11/plpgsql-statements.html * https://www.irif.fr/~zielonka/Enseignement/BDAvances/2014/PLSQL/FUNCTIONS/functions.pdf * https://www.youtube.com/watch?v=JeBr_j9O41I * http://www.postgresqltutorial.com/plpgsql-block-structure/ * https://blog.developpez.com/sqlpro/p10060/langage-sql-norme/postgresql_syntaxe_basique_des_fonctions * https://www.cri.ensmp.fr/people/coelho/cours/si/db-plpgsql-8.pdf * https://pageperso.lis-lab.fr/bernard.espinasse/Ressources-EP-BDA-GI/Poly%20anomyme-PLpgSQL.pdf ~~~sql CREATE OR REPLACE FUNCTION add(a INT,b INT) RETURNS bigint AS $$ DECLARE c int; BEGIN c:=a+b; RAISE NOTICE 'Arg1 : %',a; RAISE NOTICE 'Arg2 : %',b; RAISE NOTICE 'Return : %',c; IF c<0 THEN RAISE EXCEPTION 'Error c negativ'; END IF; RETURN c; END; $$ LANGUAGE PLPGSQL; ~~~ ### Call function Si aucun résultat n'est attendu Utiliser ''PERFORM'' à la place de ''SELECT''\\ Note : A la place de ''SELECT'' pensez à utiliser ''SELECT INTO'' Voir aussi ''EXECUTE''. ~~~sql CREATE OR REPLACE FUNCTION foo() RETURNS void AS $$ BEGIN RAISE NOTICE 'Hello from void function'; END; $$ LANGUAGE plpgsql; -- direct call from SQL SELECT foo(); -- in PLpgSQL DO $$ BEGIN SELECT foo(); -- is not allowed PERFORM foo(); -- is ok END; $$; ~~~ ~~~sql SELECT ma_function(); SELECT * FROM ma_function(); DO $$ BEGIN PERFORM ma_function(); END $$; ~~~ ### Ne rien faire Utiliser ''NULL'' ~~~sql BEGIN y := x / 0; EXCEPTION WHEN division_by_zero THEN NULL; -- ignore the error END; ~~~ ### cast conversion type - date ~~~ db1=> SELECT now(); now ------------------------------- 2019-12-11 15:54:57.984691+00 (1 row) db1=> SELECT now()::timestamp; now ---------------------------- 2019-12-11 15:55:05.608026 (1 row) ~~~ ### DEBUG Avec ''RAISE NOTICE'' ~~~sql CREATE OR REPLACE FUNCTION plop() RETURNS INT AS $$ DECLARE query1 text; WN_RESULT INT; -- res RECORD; res table%rowtype; BEGIN WN_RESULT := 1; BEGIN FOR res IN SELECT * FROM table1 WHERE maintained=false LOOP query1='UPDATE table SET nbcount=subquery.count,maintained=true FROM (SELECT COUNT(*) FROM ' || res.atable || ') AS subquery WHERE atable=' || quote_literal(res.atable) ; RAISE NOTICE 'DEBUG: %', query1 ; EXECUTE query1 ; END LOOP; EXCEPTION WHEN OTHERS THEN RAISE WARNING 'plop() exception[%][%]', SQLSTATE, SQLERRM; WN_RESULT := -1; END; RETURN WN_RESULT; END; $$ language plpgsql; SELECT plop(); ~~~