Thread: Locking rows
Hi.I'm using postgresql 8.0.3 under win2000 and developing with VS2003 (npgsql net provider).
I would like to start a transaction with a sql function.
When user press "edit" button on my form, i would lock the current row.
After user has modified data on form, pressing "save" button I would save the modified row by sql update function and so commit.
Well.The update function works fine.
My problem is to lock the current row.
My code is something like:
CREATE OR REPLACE FUNCTION "public"."new_function" () RETURNS SETOF "public"."table" AS
$body$
begin;
select * from table where field = 'value' for update;
$body$
LANGUAGE 'sql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
But I get :
ERROR , BEGIN IS NOT ALLOWED IN SQL FUNCTION
What's wrong?
Please tell me if it's a syntax problem
maybe have I to use pgsql ?
How can I lock row in pgsql ?
Thanks in advance.
I would like to start a transaction with a sql function.
When user press "edit" button on my form, i would lock the current row.
After user has modified data on form, pressing "save" button I would save the modified row by sql update function and so commit.
Well.The update function works fine.
My problem is to lock the current row.
My code is something like:
CREATE OR REPLACE FUNCTION "public"."new_function" () RETURNS SETOF "public"."table" AS
$body$
begin;
select * from table where field = 'value' for update;
$body$
LANGUAGE 'sql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
But I get :
ERROR , BEGIN IS NOT ALLOWED IN SQL FUNCTION
What's wrong?
Please tell me if it's a syntax problem
maybe have I to use pgsql ?
How can I lock row in pgsql ?
Thanks in advance.
"gabriele zelasco" <rubensoda@inwind.it> writes:
> I would like to start a transaction with a sql function.
> When user press "edit" button on my form, i would lock the current row.
> After user has modified data on form, pressing "save" button I would save t=
> he modified row by sql update function and so commit.
This is widely considered a very bad way to design an application.
Consider what happens when the user leaves for lunch, or otherwise
lets the app sit for a long time. See the list archives for prior
discussions of the issue.
But in any case, the answer to your question is to use "SELECT FOR
UPDATE" to retrieve the row. And you can't start a transaction
inside a function, because by definition you'll already be in one.
regards, tom lane