Exceptions when creating a trigger in PostgreSQL 9.1

Question

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.

Works here. Maybe you have a BOM in your file. Which editor did you use ? — wildplasser, Mar 30 '14 at 17:29
in Linux terminal. actually i'm working on stado which is a clustered db system. all sql commands work fine except for this function. — Shadin, Mar 30 '14 at 18:00
`hexdump -c myfile.sql | head` to see if there are strange characters at the beginning of the file. — wildplasser, Mar 30 '14 at 18:06
which file? i type this function direct in command line. is that okay? i also run other sql commands such as select, insert... etc. all work fine but this. — Shadin, Mar 30 '14 at 18:17
So you are working with Stado. Why is this information missing in your questions? It's the key for the solution. — Erwin Brandstetter, Mar 30 '14 at 19:21
@ErwinBrandstetter: I didn't even know about stado. BOM was just a shot from the hip ... — wildplasser, Mar 30 '14 at 19:26

score 2 · Accepted Answer · edited May 23 '17 at 11:49

2

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:

Insert text with single quotes in PostgreSQL

edited May 23 '17 at 11:49

Community

1
1

answered Mar 30 '14 at 19:32

Erwin Brandstetter

605,456
145
1,078
1,228

ooh! so just replace all ';' with '//' would solve the problem? – Shadin Mar 30 '14 at 21:25
Well, replace the respective setting for JDBC, not replace `;` in the code, which must still be valid for Postgres. That should keep JDBCC from butchering the statement at every `;`. At least this works with SQL Fiddle. I have never used Stado. – Erwin Brandstetter Mar 30 '14 at 21:54
3

Yep, you got it: PgJDBC's parser isn't currently smart enough to deal properly with dollar-quoting. Patches welcomed. – Craig Ringer Mar 31 '14 at 01:42

Exceptions when creating a trigger in PostgreSQL 9.1

1 Answers1

Linked