From: | Randall Perry <rgp(at)systame(dot)com> |
---|---|
To: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Triggers: using table's primary key value to update another field |
Date: | 2005-03-31 03:22:45 |
Message-ID: | BE70D735.61FE0%rgp@systame.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I'm trying to write a generic function that will use the primary key field
value to insert/update another field in the same table (see code below).
What I'm having trouble with is figuring out how to grab the primary key
value of the current row. I tried tacking on a var with the pkey row name to
NEW, but that doesn't work (didn't think it would).
Is there a way to do this?
CREATE FUNCTION add_parent_id () RETURNS TRIGGER AS '
DECLARE
pkey TEXT;
BEGIN
SELECT INTO pkey column_name
FROM information_schema.constraint_column_usage
WHERE table_name = TG_RELNAME
AND constraint_name ILIKE ''%_pkey'';
IF NEW.parent_id IS NULL THEN
SELECT NEW.pkey INTO NEW.parent_id;
END IF;
RETURN NEW;
END;
' LANGUAGE 'plpgsql';
--
Randall Perry
sysTame
Xserve Web Hosting/Co-location
Website Development/Promotion
Mac Consulting/Sales
From | Date | Subject | |
---|---|---|---|
Next Message | Edson Vilhena de Carvalho | 2005-03-31 03:23:15 | Database monitor (again) |
Previous Message | Edson Vilhena de Carvalho | 2005-03-31 02:56:00 | database monitor |