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
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
