Ads were blocked by your webbrowser.

This is not a problem, but keep in mind that developing HeidiSQL, user support and hosting takes time and money.

You may want to send a donation instead.

Postgres: Why does this routine fail in Heidi but ok in PGAdminIII?

roger_ackroyd's profile image roger_ackroyd posted 5 years ago in General
Copied this snippet from PostgreSQL manual. It works fine in PGadmin but fails in Heidi with this error

/* ERROR: unterminated dollar-quoted string at or near "$BODY$
DECLARE
r foo%rowtype"
LINE 2: $BODY$
^ */

Can somebody explain why please.
Here is the code:-

CREATE TABLE foo (fooid INT, foosubid INT, fooname TEXT);
INSERT INTO foo VALUES (1, 2, 'three');
INSERT INTO foo VALUES (4, 5, 'six');

CREATE OR REPLACE FUNCTION getAllFoo() RETURNS SETOF foo AS
$BODY$
DECLARE
r foo%rowtype;
BEGIN
FOR r IN SELECT * FROM foo
WHERE fooid > 0
LOOP
-- can do some processing here
RETURN NEXT r; -- return current row of SELECT
END LOOP;
RETURN;
END
$BODY$
LANGUAGE 'plpgsql' ;

SELECT * FROM getallfoo();
ansgar's profile image ansgar posted 5 years ago
Oh I guess that's an error from my regular expression based routine parser. I can check that.
ansgar's profile image ansgar posted 5 years ago
Oh no, that has nothing to do with my regular expression. But, HeidiSQL splits a batch of queries into single queries at semicolons, and fires them individually. So, after the "... r foo%rowtype", the first query ends.

HeidiSQL supports two alternative things here, which will both help you:
a) Add a "DELIMITER //" before that CREATE query:
DELIMITER //
CREATE OR REPLACE FUNCTION getAllFoos() RETURNS SETOF foo AS
$BODY$
DECLARE
r foo%rowtype;
BEGIN
FOR r IN SELECT * FROM foo
WHERE fooid > 0
LOOP
-- can do some processing here

RETURN NEXT r; -- return current row of SELECT
END LOOP;
RETURN;
END
$BODY$
LANGUAGE 'plpgsql'

b) Alternatively, activate "Send batch in one go" in the drop down menu of the blue "Play" button, before running the CREATE query:
roger_ackroyd's profile image roger_ackroyd posted 5 years ago
Thanks again Ansgar, both of those work just fine.

Please login to leave a reply, or register at first.