#320 Handle dollar signs to write stored procedure in PostgreSQL

[Anonymous]

When one writes a stored procedure for a PostgreSQL database and uses dollar signs to indicate start and end of body. SquirrelSQL returns an error:

unterminated dollar-quoted string at or near $BODY$

example stored procedure:

CREATE FUNCTION sp_removedups() RETURNS void AS
$BODY$
DECLARE lastname varchar(255);
fname varchar(255);
id bigint;
DECLARE NewListCursor CURSOR FOR
SELECT Name, id
FROM MailingList
ORDER BY Name;
OPEN NewListCursor;
LineNum := 0;
LastName := "";
FETCH NEXT FROM NewListCursor INTO fname, id;
WHILE (--Lost on variable name for end of query; EmptyQueryResponse <>
0? --)
BEGIN
IF LastName = fname THEN
DELETE FROM MailingList WHERE id = id;
END IF;
LastName := fname;
FETCH NEXT FROM NewListCursor INTO fname, id;
END;
CLOSE NewListCursor;
$BODY$
LANGUAGE 'sql' VOLATILE;

Source: http://archives.postgresql.org/pgsql-sql/2005-12/msg00087.php

[Nobody/Anonymous]

This is a PostgreSQL JDBC issue. The only other way I could think of is to use single quotes instead of dollar-signs, but you would also have to escape any single quotes in the stored procedure body with double single quotes.