synapse unsync

Postgres triggers are created in 2 stages, firstly you create the trigger function then you link the function to the trigger event. In this sample code a function that fires during before an INSERT event updates a column in the table where the data is being inserted. The function uses an internal (SQL92 compliant) string manipulation function to create the additional data that is required to complete the insert. The column has been defined as char (8), the data being manipulated is a postgres date type and must be cast to a text type. Note also that text types are indexed starting at a base of 1 (not 0 as with ‘C’ character arrays).

CREATE OR REPLACE FUNCTION set_initial_pass() RETURNS trigger AS $set_initial_pass$
DECLARE
yyyy char(4);
mm   char(2);
dd   char(2);
BEGIN
yyyy := substring(cast(NEW.dob as text) from 1 for 4);
mm   := substring(cast(NEW.dob as text) from  6 for 2);
dd   := substring(cast(NEW.dob as text) from 9 for 2);
NEW.initial_code := dd||mm||yyyy;

RETURN NEW;
END;
$set_initial_pass$
LANGUAGE 'plpgsql' VOLATILE COST 100;
ALTER FUNCTION set_initial_pass() OWNER TO script;

Now create the trigger and attach the function above.


CREATE TRIGGER set_initial_pass
BEFORE INSERT
ON person
FOR EACH ROW
EXECUTE PROCEDURE set_initial_pass();


This example was created using pgadmin III.

No comments »

Yes, it’s one of mine. It’s the entrance to Tuross Lake looking across the sandbar to the Pacific Ocean. Taken earlier this year (2008), the weather was fantastic.

Neil

No comments »