Tag: trigger

Postgres: Functions/Triggers

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.

  1.  CREATE OR REPLACE FUNCTION Public.fn_my_function(param1 integer, param2 integer, OUT Success Boolean, OUT ID integer)
  2. AS $body$
  3. DECLARE
  4.       any_variable integer;
  5. BEGIN
  6.       $3 = True;
  7.       $4 = 3234;
  8.     
  9.  
  10.       EXCEPTION 
  11.             $3 = False;
  12.             $4 = -1;
  13.             WHEN OTHERS THEN 
  14.             RAISE WARNING '[Public.fn_my_function] - UDF ERROR [OTHER] - SQLSTATE: %, SQLERRM: %',SQLSTATE,SQLERRM;
  15. END
  16. $body$ LANGUAGE plpgsql
  17. SECURITY INVOKER
  18. SET search_path = Public;

 

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

  1.  CREATE OR REPLACE FUNCTION Public.fn_get_function()
  2. RETURNS SETOF Public.My_Table AS $body$
  3. BEGIN
  4.       RETURN QUERY 
  5.       SELECT * 
  6.       FROM public.my_table t
  7.       ORDER BY t.Name;
  8. END
  9. $body$ LANGUAGE plpgsql
  10. SECURITY INVOKER
  11. SET search_path = Public;

 

Comment Function:

  1.  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.

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

 

Trigger:

  1.  CREATE OR REPLACE FUNCTION Public.fn_my_function() RETURNS TRIGGER AS $BODY$
  2. DECLARE
  3.     v_old_data json;
  4.     v_new_data json;
  5. BEGIN
  6.     IF (TG_OP = 'UPDATE') THEN
  7.       v_old_data := row_to_json(OLD.*);
  8.       v_new_data := row_to_json(NEW.*);
  9.       RETURN NEW;
  10.     ELSIF (TG_OP = 'DELETE') THEN
  11.       v_old_data := row_to_json(OLD.*);
  12.       RETURN OLD;
  13.     ELSIF (TG_OP = 'INSERT') THEN
  14.       v_new_data := row_to_json(NEW.*);
  15.       RETURN NEW;
  16.     ELSE
  17.       RAISE WARNING '[Public.fn_my_function] - Other action occurred: %, at %',TG_OP,now();
  18.       RETURN NULL;
  19.     END IF;
  20.        
  21.     EXCEPTION
  22.       WHEN data_exception THEN
  23.             RAISE WARNING '[Public.fn_my_function] - UDF ERROR [DATA EXCEPTION] - SQLSTATE: %, SQLERRM: %',SQLSTATE,SQLERRM;
  24.             RETURN NULL;
  25.       WHEN unique_violation THEN
  26.             RAISE WARNING '[Public.fn_my_function] - UDF ERROR [UNIQUE] - SQLSTATE: %, SQLERRM: %',SQLSTATE,SQLERRM;
  27.             RETURN NULL;
  28.       WHEN OTHERS THEN
  29.             RAISE WARNING '[Public.fn_my_function] - UDF ERROR [OTHER] - SQLSTATE: %, SQLERRM: %',SQLSTATE,SQLERRM;
  30.             RETURN NULL;
  31. END;
  32. $BODY$
  33. LANGUAGE plpgsql
  34. SECURITY INVOKER
  35. SET search_path = Public;

Postgres: Tables

Below are some common functions for doing table creation and maintenance.

Table Creation:

  1.  CREATE TABLE Public.mytable (
  2.       id BigSerial PRIMARY KEY,
  3.       text_column varchar NOT NULL,
  4.       int_column Integer NOT NULL,
  5.       date_column timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
  6. );

Create Schema:

  1.  CREATE SCHEMA IF NOT EXISTS test;

Create Schema with Authorization:

  1. CREATE SCHEMA IF NOT EXISTS test AUTHORIZATION myUser;

Drop Schema Cascade:

  1. DROP SCHEMA IF EXISTS test CASCADE;

Comment On Table:

  1.  COMMENT ON TABLE Public.mytable IS 'A List of data.';

Vacuum:
vacuum has options best to review them.

  1.  vacuum (analyze,verbose);

Drop Constraint:

  1.  ALTER TABLE mytable DROP CONSTRAINT mytable_id_pkey;

Add Constraint:

  1.  ALTER TABLE public.mytable ADD CONSTRAINT mytable_id_pkey PRIMARY KEY (id);

Rename Constraint:

  1.  ALTER TABLE mytable RENAME CONSTRAINT "mytable_id2_fkey" TO "mytable_id3__fkey";

Rename Table Column:

  1.  ALTER TABLE mytable RENAME COLUMN text_column TO text_column2;

Rename Table:

  1.  ALTER TABLE mytable RENAME TO mytable2;

Drop Table:

  1.  DROP TABLE public.mytable;

Add Column to Table:

  1.  ALTER TABLE Public.mytable ADD column_name boolean NOT NULL DEFAULT False;

Alter Column Data Type Json:

  1. ALTER TABLE public.mytable ALTER COLUMN json_col TYPE json USING (json_col::json);

Rename Sequence:

  1.  ALTER SEQUENCE mytable_id_seq RENAME TO mytable_id_seq;

Sequence Table Owner:

  1.  alter sequence mytable_id_seq owned by mytable.id;

Sequence Next Value:

  1.  alter table mytable alter column mytable_id set default nextval('mytable_id_seq');

Add Foreign Key:

  1.  alter table mytable ADD FOREIGN KEY (foreign_id) REFERENCES public.mytable2(foreign_id);

Create Index Json:

  1.  CREATE INDEX mytable_idx ON Public.mytable((Data->'Key'->'Key'->>'value'));

Create Index:

  1.  CREATE INDEX mytable_idx ON public.mytable(id);

Drop Index:

  1.  DROP INDEX public.mytable_idx;

Re-Cluster Table:

  1.  Cluster mytable using mytable_pkey;

Trigger:

  1.  CREATE TRIGGER "tg_mytrigger" BEFORE UPDATE OF my_column OR INSERT ON public.mytable FOR EACH ROW EXECUTE PROCEDURE public.mytablestrigger();