Problem executing PgSqlScript for creating postgres sql function

Problem executing PgSqlScript for creating postgres sql function

Postby jules3766 » Fri 25 Mar 2016 06:04

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
jules3766
 
Posts: 5
Joined: Fri 25 Mar 2016 05:52

Re: Problem executing PgSqlScript for creating postgres sql function

Postby Shalex » Fri 25 Mar 2016 15:13

We have reproduced the issue. We will investigate it and notify you about the result.
Shalex
Devart Team
 
Posts: 7654
Joined: Thu 14 Aug 2008 12:44

Re: Problem executing PgSqlScript for creating postgres sql function

Postby Shalex » Tue 26 Apr 2016 17:21

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.
Shalex
Devart Team
 
Posts: 7654
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for PostgreSQL