Thread: Triggers: using table's primary key value to update another field

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

http://www.systame.com/

"Randall Perry" <rgp@systame.com> writes
> 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).

There is an example in PG docs.

http://www.postgresql.org/docs/8.0/static/trigger-example.html

Regards,
Qingqing