plugins.rawSQL with PostgreSQL

Classic Servoy
1

When a stored procedure is created or replaced with with plugins.rawSQL we get the following error:

org.postgresql.util.PSQLException: ERROR: unterminated dollar-quoted string at or near "$
BEGIN
RETURN 1" ERROR: unterminated dollar-quoted string at or near "$
BEGIN
RETURN 1"

The sql sent to the server is:

"CREATE OR REPLACE FUNCTION idf_test() RETURNS integer AS $
BEGIN
RETURN 1;
END;
$ LANGUAGE plpgsql;"

Should a different string then $$ surrounding the procedure implementation be used?

When copied/pasted to command line psql the procedure is created correctly.

2

Hi Michel,

Per http://www.postgresql.org/docs/8.2/inte … -tips.html (comments at the bottom), use apostroph instead of $$.
So the string send to the database should be:

"CREATE OR REPLACE FUNCTION idf_test() RETURNS integer AS '
BEGIN
RETURN 1;
END;
' LANGUAGE plpgsql;"
3

Michel,

I did this and got the error Error: ERROR: language “plpgsql” does not exist

This was fixed from the command line:

cd /path/to/servoy/application_server
./postgres_db/bin/createlang -U DBA -d example plpgsql

Rob

4

@Robert
Thanks for the prompt response! Tested it and it works! I figured there was another way to do this, but I could not find it.

@Rob
Also ran into this problem. Did the same you did, but issued the command in psql (CREATE LANGUAGE plpgsql;).

5

If you want to have PL/PgSQL installed in all your databases that you will be creating you can simply add it to the database named ‘template1’. PostgreSQL uses this database as a template to create a new one.

Note that you can use any database as a template, so very handy if you want to duplicate a production database (with data and all) to have a test environment.
The syntax for this is:

CREATE DATABASE myNewDB TEMPLATE myExistingDB;

TIP: if you screw up the database ‘template1’ you can drop it and create a new one using (for obvious reasons, read-only) database ‘template0’.

Hope this helps.