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