Converting stored procedures from SQL Anywhere to PostGres.

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