Différences

Ci-dessous, les différences entre deux révisions de la page.

--- tech:notes_postgres_pl_pgsql [2025/03/24 15:06] – créée - modification externe 127.0.0.1
+++ tech:notes_postgres_pl_pgsql [2025/05/25 18:48] (Version actuelle) – Jean-Baptiste
@@ Ligne 1: / Ligne 1: @@
+<!DOCTYPE markdown>
+{{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
+-------------------------------
+-12-11 15:54:57.984691+00
+(1 row)
+db1=> SELECT now()::timestamp;
+            now
+----------------------------
+-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();
+~~~