Problem executing PgSqlScript for creating postgres sql function

[jules3766]

This problem has just shown up with build 7.4.616.0.

We want to execute this script/create function:

CREATE OR REPLACE FUNCTION tools.text_to_int (
chartoconvert text
)
RETURNS integer AS
$body$
SELECT
CASE WHEN trim(chartoconvert) SIMILAR TO '[0-9,]+'
THEN CAST(trim(REPLACE(chartoconvert,',','')) AS integer)
ELSE NULL END;
$body$
LANGUAGE 'sql'
IMMUTABLE
RETURNS NULL ON NULL INPUT
SECURITY INVOKER
COST 100;

When we use PgSqlScript to execute the previous sql the function it will fail.

unterminated dollar-quoted string at or near "$body$
SELECT
CASE WHEN trim(chartoconvert) SIMILAR TO '[0-9,]+'
THEN CAST(trim(REPLACE(chartoconvert,',','')) AS integer)
ELSE NULL END"

We do have plpgsql function that get run right before this one that works just fine. So this looks like it might be just related to function written in the sql language and not plpgsql. While these are the only two languages I am working with you might want to check the other languages such as plperl, plr, etc.

Julie

[Shalex]

We have reproduced the issue. We will investigate it and notify you about the result.

[Shalex]

The bug with executing query in PgSQLScript containing function declarations is fixed in the newest (7.5.639) build of dotConnect for PostgreSQL: http://forums.devart.com/viewtopic.php?f=3&t=33573.