accessing currval(), How? ... Trigger? I think...???
От | Ralph Rotondo |
---|---|
Тема | accessing currval(), How? ... Trigger? I think...??? |
Дата | |
Msg-id | BL12a.24708$F25.15310@nwrddc02.gnilink.net обсуждение исходный текст |
Ответы |
Re: accessing currval(), How? ... Trigger? I think...???
|
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: