Disable Autocommit in PostgreSQL to Create a Trigger

Question

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.

Community · Accepted Answer · 2017-05-23 12:28:49Z

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

edited May 23, 2017 at 12:28

CommunityBot

11 silver badge

answered Mar 29, 2014 at 19:14

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Shadin Over a year ago

So i have to combine the two parts? please check my updated post

Erwin Brandstetter Over a year ago

@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.

Shadin Over a year ago

i fixed it as you mentioned (in the post) but the connection closed every time i run the command.

Erwin Brandstetter Over a year ago

@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?

Shadin Over a year ago

I post another question with more details. would you please check it out. stackoverflow.com/questions/22747225/…

Collectives™ on Stack Overflow

Disable Autocommit in PostgreSQL to Create a Trigger

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related