Thread: returning value from inside a transaction

create table tez (i int);
insert into tez values(3);

create function tezt0(int) returns int as '
begin work;
        lock table tez;
        select * from tez as r;
commit work;

select tez.i;
' language 'sql';

select tezt0(1);
 tezt0
-------
     3
(1 row)

create function tezt1(int) returns timestamp as '
begin work;
        lock table tez;
        select now() as n;
commit work;
select n;
' language 'sql';
ERROR:  Attribute 'n' not found

ok so it's a visibility error in my instance here but how can i get the
value returned from a function inside the transaction

a wrapper like this is the only way I can see to lock tables a 'plpgsql'
function updates.

"Functions and trigger procedures are always executed within a transaction
established by an outer query"

so how on earth do i do this and find out what my functions return!

TIA

Matt

On Wed, 12 Sep 2001, Matt wrote:

> create table tez (i int);
> insert into tez values(3);
>
> create function tezt0(int) returns int as '
> begin work;
>         lock table tez;
>         select * from tez as r;
> commit work;
>
> select tez.i;
> ' language 'sql';

It looks to me from testing reasonably recent sources
that the above really isn't safe.  Since we don't have nested
transactions, that's not a separate transaction from whatever
it's running in, but instead the begin will NOTICE if you're in a
transaction and the commit will commit it and now you're no longer
in a transaction...
 begin;
  select tezt0(4);
  insert into tez values (4);
 rollback;
will end up with tez getting the row and a notice about the fact
you weren't in a transaction from rollback.  Is this really reasonable
behavior?