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
– wildplasser, Commented Mar 30, 2014 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
– Shadin, Commented Mar 30, 2014 at 18:00
hexdump -c myfile.sql | head to see if there are strange characters at the beginning of the file. — wildplasser
– wildplasser, Commented Mar 30, 2014 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
– Shadin, Commented Mar 30, 2014 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
– Erwin Brandstetter, Commented Mar 30, 2014 at 19:21

Community · Accepted Answer · 2017-05-23 11:49:53Z

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, 2017 at 11:49

CommunityBot

11 silver badge

answered Mar 30, 2014 at 19:32

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

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

3 Comments

Shadin Over a year ago

ooh! so just replace all ';' with '//' would solve the problem?

Erwin Brandstetter Over a year ago

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.

Craig Ringer Over a year ago

Yep, you got it: PgJDBC's parser isn't currently smart enough to deal properly with dollar-quoting. Patches welcomed.

Collectives™ on Stack Overflow

Exceptions when creating a trigger in PostgreSQL 9.1

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related