From: | Bhavesh Jardosh <perltastic(at)yahoo(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Help with BEGIN/COMMIT within a transaction |
Date: | 2003-08-01 21:31:08 |
Message-ID: | 20030801213108.55215.qmail@web21604.mail.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I aplogoize in the first place, if this is a silly question. But as silly as it
sounds it has been giving me a hard time.
I need to use BEGIN/COMMIT within a stored procedure and almost all the syntax
(e.g. BEGIN ... COMMIT, START ... COMMIT, BEGIN WORK ... COMMIT WORK etc.)
gives me an error when I try to execute (not when I compile) the stored
procedure.
Attached is the script to reproduce the problem.
Is there a compile time option or a server setting that I need to enable ?
It does COMMIT when it exits from the stored procedure, but thats not I want. I
want to commit from within a cursor loop so that the changes are visible in
other sessions as soon as they are done.
=== create table script ==
create table employee
(
id integer,
name text
);
=== stored procedure =====
CREATE OR REPLACE FUNCTION sp_test() RETURNS TEXT AS '
BEGIN
START
INSERT INTO employee (id, name) values (1, ''postgres'');
COMMIT;
return ''OK'';
END;
' LANGUAGE plpgsql;
=== invoking the stored procedure thru psql ===
test=# select sp_test() as status;
WARNING: plpgsql: ERROR during compile of sp_test near line 3
ERROR: parse error at or near ";"
test=#
__________________________________
Do you Yahoo!?
The New Yahoo! Search - Faster. Easier. Bingo.
http://search.yahoo.com
From | Date | Subject | |
---|---|---|---|
Next Message | MT | 2003-08-01 21:45:54 | Dealing with commas in fields |
Previous Message | elein | 2003-08-01 20:59:13 | Re: CREATE TABLE with a column of type {table name} |