Thread: accessing currval(), How? ... Trigger? I think...???

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

On Tue, 2003-02-11 at 07:38, Ralph Rotondo wrote:

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

There's nothing special about accessing currval from outside
postgresql.  psql uses the libpq interface, just like everything else.

I wonder if your problem is that you are not getting the same database
session each time?  currval() only works in the *same* session that
nextval() was called in.  Since http is stateless, you would need to
provide a means to connect to the same session; any other would give a
failure, or even the wrong result (if someone else had called nextval
and you had inherited that session).  Perhaps you should arrange to
store the nextval result with a key that can be retrieved from any
session.

If it is the session that is the problem, wrapping currval in a function
will make no difference at all.

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight, UK                             http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "But as many as received him, to them gave he power to
      become the sons of God, even to them that believe on
      his name"        John 1:12