I've got a few plpgsql stored functions (in 7.4.x) that use the
select x into y from table where condition for update
syntax without any problem.
Maybe there's something else going on?
John Sidney-Woollett
Eric Brown wrote:
> I'm trying to write a stored procedure in plpgsql that selects a row and
> possibly increments one of its fields. I thought I would do SELECT INTO
> my_record * FROM my_table FOR UPDATE WHERE ..., but apparently plpgsql
> doesn't like the FOR UPDATE in a stored procedure. Does plpgsql
> automatically lock any rows I read until the stored procedure exits? I'm
> just not sure how to get the functionality I'm looking for and not have
> to concern myself with concurrency.
>
> Example:
> create table t_test (x int, y int);
> create or replace function f_test(int) returns void as '
> declare r record;
> begin
> select into r *, oid from t_test -- FOR UPDATE
> where x = $1;
> if found then
> update t_test set y=y+1 where oid = r.oid;
> end if;
> return;
> end' language plpgsql;
> insert into t_test values (1,1);
> select f_test(1);
>
