Thread: Converting stored procedures from SQL Anywhere to PostGres.
I am trying to convert stored procedures from SQL Anywhere to Postgres.
I am getting error
ERROR: syntax error at or near "return"
LINE 2: return integer AS
^
********** Error **********
ERROR: syntax error at or near "return"
SQL state: 42601
Character: 81
when I run this. I'm not sure what the problem is here. Any help would
be appreciated.
CREATE OR REPLACE FUNCTION "iss"."ConfigAddKey" (in
isscontrib.citext,pkeyname)
return integer AS
$BODY$
begin
declare l:retval integer;
declare l:id integer;
if exists(select id into l:id from iss.configkeys where keyname =
pkeyname) then
l:retval := l:id
else
insert into "iss".configkeys(keyname) values (pKeyname);
end if;
return retval
end;
$BODY$
LANGUAGE 'plpgsql';
Michael Gould
Intermodal Software Solutions, LLC
904-226-0978
On Wed, Mar 7, 2012 at 12:51 PM, <mgould@isstrucksoftware.net> wrote: > I am trying to convert stored procedures from SQL Anywhere to Postgres. > I am getting error > > ERROR: syntax error at or near "return" > LINE 2: return integer AS > ^ > > > ********** Error ********** > > ERROR: syntax error at or near "return" > SQL state: 42601 > Character: 81 > > when I run this. I'm not sure what the problem is here. Any help would > be appreciated. > > > CREATE OR REPLACE FUNCTION "iss"."ConfigAddKey" (in > isscontrib.citext,pkeyname) > return integer AS > $BODY$ > begin > > declare l:retval integer; > declare l:id integer; > > if exists(select id into l:id from iss.configkeys where keyname = > pkeyname) then > l:retval := l:id > else > insert into "iss".configkeys(keyname) values (pKeyname); > end if; > return retval > > end; > $BODY$ > LANGUAGE 'plpgsql'; Well, the languages are obviously not compatible. You're going to have to convert them all and to do that you're going to have a decent understanding of both languages. I doubt there are any automatic tools to do it. start here: http://www.postgresql.org/docs/current/static/plpgsql.html merlin
On 03/07/2012 10:51 AM, mgould@isstrucksoftware.net wrote: > I am trying to convert stored procedures from SQL Anywhere to Postgres. > I am getting error > > ERROR: syntax error at or near "return" > LINE 2: return integer AS > ^ > > > ********** Error ********** > > ERROR: syntax error at or near "return" > SQL state: 42601 > Character: 81 > > when I run this. I'm not sure what the problem is here. Any help would > be appreciated. > > > CREATE OR REPLACE FUNCTION "iss"."ConfigAddKey" (in > isscontrib.citext,pkeyname) > return integer AS ^^^ Should be returns For a good overview see: http://www.postgresql.org/docs/9.0/interactive/plpgsql-structure.html > $BODY$ > begin > > declare l:retval integer; > declare l:id integer; > > if exists(select id into l:id from iss.configkeys where keyname = > pkeyname) then > l:retval := l:id > else > insert into "iss".configkeys(keyname) values (pKeyname); > end if; > return retval > > end; > $BODY$ > LANGUAGE 'plpgsql'; > > Michael Gould > Intermodal Software Solutions, LLC > 904-226-0978 > > -- Adrian Klaver adrian.klaver@gmail.com