Help with BEGIN/COMMIT within a transaction

Поиск
Список
Период
Сортировка
От Bhavesh Jardosh
Тема Help with BEGIN/COMMIT within a transaction
Дата
Msg-id 20030801213108.55215.qmail@web21604.mail.yahoo.com
обсуждение исходный текст
Список 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

В списке pgsql-general по дате отправления:

Предыдущее
От: Ian Harding
Дата:
Сообщение: Re: Apache - DBI - Postgresql: Cancelling queries
Следующее
От: Tom Lane
Дата:
Сообщение: Re: CREATE TABLE with a column of type {table name}