Trigger functions can only take text as arguments (and have to be declared with no arguments at all) (surprise-surprise
:-)
The upside though, is that they also have access to the tuples that triggered the execution... I think, something like
thisshould
work (but not sure about the syntax - neevr used plpgsql):
create function contact_list_trigger() returns opaque as
'begin; insert into contact_lists (contact_id) values (new.id); return new; end;'
language 'plpgsql';
or (without using the "new.id"):
create function contact_list_trigger returns opaque as
'begin; insert into contact_lists(contact_id) values (currval(\'contact_id_seq\')); return new;end;'
language 'plpgsql';
... and then:
create trigger insert_contact_list after insert on contacts for each row execute procedure contact_list_trigger();
I hope, it helps...
Dima.
Ralph Rotondo wrote:
> Hello.
>
> I am in the process of porting some old db solutions into PostgreSQL. One
> thing I did alot in my old environment was:
>
> when creating a new record in table A
> automatically create a related record in table B
>
> Here's the example I'm trying to create.
>
> Table contacts has a PRIMARY key named contact_id (serial)
> - it gets it's value from nextval('"contact_id_seq"'::text)
>
> What I want to do is take the value used for contact_id by the sequence
> contact_id_seq and insert it into a matching field in table contact_lists,
> (In other words I want everybody entered in the db to get a contact_list
> assigned to them linked via their contact_id).
>
> I can do this from the commandline using:
>
> SELECT currval('"contact_id_seq"');
>
> I have had no success accessing the currval() function through PHP trying
> every possible combo of single & double quotes and have reached the
> conclusion that the currval() function is simply unreachable from outside
> postgres. So I tried to create a pl/pgsql function to be called by a
> trigger.
>
> Here is one of many attempts to make that work:
>
> CREATE FUNCTION "contact_list_trigger" (bigint) RETURNS opaque AS '
> declare
> curr_val alias for $1;
> begin
> insert into contact_lists (contact_id) values(currval);
> return new;
> end;
> ' LANGUAGE 'plpgsql';
>
> CREATE TRIGGER "insert_contact_list" AFTER INSERT ON "contacts" FOR EACH
> ROW EXECUTE PROCEDURE "contact_list_trigger" (
> 'currval("contact_id_seq")');
>
> ... And that's about as far as I can possibly take it. Any help at all would
> be greatly appriciated. Thank you.
>