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

Search Discussions

  • Doug McNaught at Aug 2, 2003 at 1:42 pm

    Bhavesh Jardosh writes:

    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.
    You can't currently do this. Calls to functions are already inside a
    (possibly implicit) transaction, so calling BEGIN inside a function
    would have to start a subtransaction, which PG doesn't currently
    support. There is some possibility that this feature will be in 7.5,
    but that won't be coming out anmytime soon.

    -Doug
  • Manfred Koizar at Aug 4, 2003 at 7:50 pm

    On 02 Aug 2003 09:42:05 -0400, Doug McNaught wrote:
    Bhavesh Jardosh <perltastic@yahoo.com> writes:
    I need to use BEGIN/COMMIT within a stored procedure [...]
    You can't currently do this. Calls to functions are already inside a
    (possibly implicit) transaction, so calling BEGIN inside a function
    would have to start a subtransaction, which PG doesn't currently
    support. There is some possibility that this feature will be in 7.5,
    but that won't be coming out anmytime soon.
    Subtransactions won't help, because the OP wants
    [...] to commit from within a cursor loop so that the changes are visible in
    other sessions as soon as they are done.
    Changes will not be visible to other sessions until the *main*
    transaction commits.

    Servus
    Manfred

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-general @
categoriespostgresql
postedAug 1, '03 at 9:31p
activeAug 4, '03 at 7:50p
posts3
users3
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase