Re: Problem with curval

I am using something similar to the following code in Access to get the
currval:
        conn.Execute ("Insert into Shipments(...) values(...)")
        rs.Open "select currval('shipments_shipmentid_seq') as shipmentid;",
conn, adOpenForwardOnly, adLockReadOnly
where rs is my ADODB.RecordSet and conn is an ADODB.Connection

In your case it looks like you're trying to build a function to mimic the
MySQL last_insert_id function.
You should not be using a loop. You only want one value.
your code should look like:

create or replace function last_insert_id(varchar,varchar ) returns integer
  as
'
 declare
 intable alias for $1;
 incolumn alias for $2;
 myvalue integer;
begin
    select  currval('''' ''  || intable || ''_'' || incolumn || ''_seq'''')
into myvalue;
    return myvalue;
end
'
 language plpgsql;

Your SQL code to retrieve it wsould be simply "select
last_insert_id(var1,var2)"


"Mike Fahey" <mfahey@enter.net> wrote in message
news:419386D5.9060502@enter.net...
> This wont work because myvalue must be a record or row variable.
>
> I'm guessing its nearly impossible to get the last inserted id from
> access 2003 using
> postgres odbc.
>
> select @@IDENTITY always returns "2".
>
> Thoughts?
>
>
>
>
> create function last_insert_id(varchar,varchar ) returns integer
>  as '
> declare
> intable alias for $1;
> incolumn alias for $2;
> myvalue integer;
>
> BEGIN
>
> for myvalue in execute  '' select  currval('''' ''
>  || intable || ''_'' || incolumn
>  || ''_seq'''')
> '' Loop
>     return myvalue;
>
>
> END loop;
> END;
>
> '
>  language plpgsql;
>
>
>
> --
> With best regards,
>
> Mike Fahey - Systems Administration
> ********************************************************************
>       ENTER.NET - "The Road to the Internet Starts Here!" (tm)
>   (610) 437-2221 * http://www.enter.net/ * email:support@enter.net
> ********************************************************************
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>