Обсуждение: Help with BEGIN/COMMIT within a transaction

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

Bhavesh Jardosh <perltastic@yahoo.com> 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

On 02 Aug 2003 09:42:05 -0400, Doug McNaught <doug@mcnaught.org>
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