CODEWITHSUNDEEP
databasepostgresqltriggersstado
Shadin
Shadin

Reputation: 1967

Exceptions when creating a trigger in PostgreSQL 9.1

I use PostgreSQL 9.1 with PostGIS 1.5.

I'm trying to get this trigger function to work in terminal (Ubuntu):

CREATE FUNCTION insert_trigger()
RETURNS trigger AS
$insert_trigger$
BEGIN
IF ( NEW.id >= 10 AND NEW.id < 100 ) THEN 
INSERT INTO part_id_p10 VALUES (NEW.*); 
ELSIF ( NEW.id >= 100 AND NEW.id < 200 ) THEN 
INSERT INTO part_id_p20 VALUES (NEW.*);
ELSE
RAISE EXCEPTION 'id out of range.  Something wrong with the insert_trigger() function!';
END IF;
RETURN NULL;
END
$insert_trigger$ LANGUAGE plpgsql;

i get this exceptions:

SQLException: ERROR: Encountered "FUNCTION" at line 1, column 8.

SQLException: ERROR: Encountered "ELSIF" at line 1, column 1.

SQLException: ERROR: Encountered "ELSE" at line 1, column 1.

SQLException: Cannot commit when autoCommit is enabled.

SQLException: ERROR: Encountered "RETURN" at line 1, column 1.

SQLException: Cannot commit when autoCommit is enabled.

Upvotes: 1

Views: 1441

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656546

I quote what I found in online documentation:

Stado is written in Java and communicates with the underlying databases via JDBC.

Bold emphasis mine. Based on this, let me present a this hypothesis:

Many here know the website SQL Fiddle. It uses JDBC, too. Here is what happens when I try to create your function in default mode:

Failing fiddle

But this one works:

Working Fiddle

The difference? I changed the "Query Terminator" (bottom right) from ; to // to keep JDBC from butchering the statement. Obviously, JDBC cannot (yet) deal with dollar-quoting correctly. See @Craig's comment below.

You can circumvent the problem by using a query terminator that does not show up in your code, like in my fiddle. Or replace dollar-quotes with plain single-quotes. You'll have to escape every single-quote properly, though:

Upvotes: 2

Related Questions