Help with BEGIN/COMMIT within a transaction

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

Responses

Browse pgsql-general by date

  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}