Postgres Pro
Downloads
Home   >   mailing lists

Thread: Problem with curval

Problem with curval

From
Mike Fahey
Date:
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
********************************************************************

Re: Problem with curval

From
Jeff Eckermann
Date:
--- Mike Fahey <mfahey@enter.net> wrote:

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

myvalue record;

>
> BEGIN
>
> for myvalue in execute  '' select  currval('''' ''
>  || intable || ''_'' || incolumn
>  || ''_seq'''')
> '' Loop

as myalias'' Loop

>     return myvalue;

return myvalue.myalias;

(you may need to do some casting, but I suspect that
plpgsql will take care of that for you)

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




__________________________________
Do you Yahoo!?
Check out the new Yahoo! Front Page.
www.yahoo.com

Re: Problem with curval

From
"Sim Zacks"
Date:
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
>