Notes Postgres PL/PGSQL

Voir :

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