0

I tried to write a trigger in PostgreSQL 9.1 database, something like:

CREATE FUNCTION test_trigger()
RETURNS trigger AS
BEGIN
IF () THEN INSERT INTO...;
ELSEIF ()...;
...
...
END IF;
RETURN NULL;
END

Then I got this error:

Cannot commit when autoCommit is enabled error

So I tried to disable it using set autocommit=off; but then got this message:

ERROR:  SET AUTOCOMMIT TO OFF is no longer supported

One suggested solution was to use BEGIN to start a transaction, but I have no idea how to edit my trigger function to do that.

UPDATE

I tried this to disable autocommit with trigger creation as follows:

BEGIN;
CREATE FUNCTION test_trigger()
RETURNS trigger AS
$func$
BEGIN
IF () THEN INSERT INTO...;
ELSEIF ()...;
...
...
END IF;
RETURN NULL;
END
COMMIT; 
$func$ 
LANGUAGE plpgsql;

but the connection closed every time i run this.

Shadin
  • 1,867
  • 5
  • 26
  • 37

1 Answers1

3

This might be a misunderstanding. Autocommit doesn't seem to be the problem.

The function body is a string, you need to quote it. Typically you would use dollar-quoting to make your live easier:

CREATE FUNCTION test_trigger()
RETURNS trigger AS
$func$
BEGIN
IF () THEN INSERT INTO...;
ELSEIF ()...;
...
...
END IF;
RETURN NULL;
END
$func$ LANGUAGE plpgsql;

Apart from that, to "disable" autocommit, start a transaction. That's what the suggested solution in your last line aims for:

BEGIN;               -- starts transaction
UPDATE tbl ...;      -- not commited yet
CREATE FUNCTION ...; -- not commited yet
COMMIT;              -- *now* we commit
Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • So i have to combine the two parts? please check my updated post – Shadin Mar 30 '14 at 09:28
  • @Oxi: As stated, I don't think, autocommit is the problem at all. Just fix the create function statement. And you cannot start /stop transaction in the function body. A function is always atomic. – Erwin Brandstetter Mar 30 '14 at 09:56
  • i fixed it as you mentioned (in the post) but the connection closed every time i run the command. – Shadin Mar 30 '14 at 10:02
  • @Oxi: With the same error message? Add more information to your question. (Not just in comments.) How do you connect? What is your client? Your exact version of Postgres? Does the function get created? What's in your db log? – Erwin Brandstetter Mar 30 '14 at 14:06
  • I post another question with more details. would you please check it out. http://stackoverflow.com/questions/22747225/exceptions-when-creating-a-trigger-in-postgresql-9-1 – Shadin Mar 30 '14 at 17:26