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

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.

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; $$;
SELECT ma_function(); SELECT * FROM ma_function(); DO $$ BEGIN PERFORM ma_function(); END $$;

Ne rien faire

Utiliser NULL

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

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();