Обсуждение: stored proc help

Поиск
Список
Период
Сортировка

stored proc help

От
"Jason Tesser"
Дата:

I have the following store dproc but when I run it I am getting the error

 

ERROR:  invalid input syntax for integer: "(1)"

CONTEXT:  PL/pgSQL function "irispermissionget" line 9 at return next

 

What am I doing wrong?

 

CREATE OR REPLACE FUNCTION "public"."irispermissionget" (username varchar, pass varchar) RETURNS SETOF integer AS

$body$

declare

returnRec Record;

 

begin

for returnRec in select cast(irisuserpermission.permissionid as integer)

from irisuserpermission INNER JOIN public.irisuser ON (public.irisuserpermission.irisuserid = public.irisuser.id)

where irisuser.user = username and irisuser.password = pass

loop

             return next returnRec;

     end loop;

     return;

end;

$body$

LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

Re: stored proc help

От
Michael Fuhr
Дата:
On Wed, Jul 13, 2005 at 01:28:45PM -0500, Jason Tesser wrote:
> I have the following store dproc but when I run it I am getting the
> error
>
> ERROR:  invalid input syntax for integer: "(1)"
> CONTEXT:  PL/pgSQL function "irispermissionget" line 9 at return next
>
> What am I doing wrong?

The function is declared to return SETOF INTEGER, but you return a
RECORD variable:

>              return next returnRec;

Try returning returnRec.permissionid instead.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/