From: | Eric Brown <eric(dot)brown(at)propel(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | pgplsql SELECT INTO ... FOR UPDATE (transaction/locking question) |
Date: | 2004-12-15 09:38:53 |
Message-ID: | 21E1C66B-4E7D-11D9-991E-000A95C7176C@propel.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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);
From | Date | Subject | |
---|---|---|---|
Next Message | John Sidney-Woollett | 2004-12-15 10:27:35 | Re: pgplsql SELECT INTO ... FOR UPDATE (transaction/locking |
Previous Message | Richard Huxton | 2004-12-15 09:07:45 | Re: Running functions that return void in psql |