Postgres: Functions/Triggers

(Last Updated On: )

I usually use pgsql to write my functions.

Example Update Function:
This function could be used as a save/creation of records. It passes back a boolean and id.

 CREATE OR REPLACE FUNCTION Public.fn_my_function(param1 integer, param2 integer, OUT Success Boolean, OUT ID integer)
AS $body$
DECLARE
      any_variable integer;
BEGIN
      $3 = True;
      $4 = 3234;
    
 
      EXCEPTION 
            $3 = False;
            $4 = -1;
            WHEN OTHERS THEN 
            RAISE WARNING '[Public.fn_my_function] - UDF ERROR [OTHER] - SQLSTATE: %, SQLERRM: %',SQLSTATE,SQLERRM;
END
$body$ LANGUAGE plpgsql
SECURITY INVOKER
SET search_path = Public;

 

Get Function:
You can also return a table.
IE: RETURNS TABLE(id integer, data json) AS $body$

 CREATE OR REPLACE FUNCTION Public.fn_get_function()
RETURNS SETOF Public.My_Table AS $body$
BEGIN
      RETURN QUERY 
      SELECT * 
      FROM public.my_table t
      ORDER BY t.Name;
END
$body$ LANGUAGE plpgsql
SECURITY INVOKER
SET search_path = Public;

 

Comment Function:

 COMMENT ON FUNCTION Public.fn_my_function(integer, integer) IS '';

 

Drop Function:
Notice how I use “IF EXISTS”. This is best because if it didn’t exist your script would fail.

 DROP FUNCTION IF EXISTS Public.fn_my_function(Integer, Integer, Integer);

 

Trigger:

 CREATE OR REPLACE FUNCTION Public.fn_my_function() RETURNS TRIGGER AS $BODY$
DECLARE
    v_old_data json;
    v_new_data json;
BEGIN
    IF (TG_OP = 'UPDATE') THEN
      v_old_data := row_to_json(OLD.*);
      v_new_data := row_to_json(NEW.*);
      RETURN NEW;
    ELSIF (TG_OP = 'DELETE') THEN
      v_old_data := row_to_json(OLD.*);
      RETURN OLD;
    ELSIF (TG_OP = 'INSERT') THEN
      v_new_data := row_to_json(NEW.*);
      RETURN NEW;
    ELSE
      RAISE WARNING '[Public.fn_my_function] - Other action occurred: %, at %',TG_OP,now();
      RETURN NULL;
    END IF;
       
    EXCEPTION
      WHEN data_exception THEN
            RAISE WARNING '[Public.fn_my_function] - UDF ERROR [DATA EXCEPTION] - SQLSTATE: %, SQLERRM: %',SQLSTATE,SQLERRM;
            RETURN NULL;
      WHEN unique_violation THEN
            RAISE WARNING '[Public.fn_my_function] - UDF ERROR [UNIQUE] - SQLSTATE: %, SQLERRM: %',SQLSTATE,SQLERRM;
            RETURN NULL;
      WHEN OTHERS THEN
            RAISE WARNING '[Public.fn_my_function] - UDF ERROR [OTHER] - SQLSTATE: %, SQLERRM: %',SQLSTATE,SQLERRM;
            RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql
SECURITY INVOKER
SET search_path = Public;