Thread: PL/pgSQL question

Hi All!
I'm trying to get working the below PL/pgSQL function without sucess.
The function is correctly created, but when I tested it i got:

# SELECT grantAccess('sara', 'sarapass');
ERROR:  set-valued function called in context that cannot accept a set
CONTEXT:  PL/pgSQL function "grantaccess" line 10 at return next
veillewm=#

What I'm missing?
Thank in advance
Younes

----------------- CODE BEGIN --------------------------
CREATE FUNCTION grantAccess(text,text) RETURNS SETOF users AS '
  DECLARE
     userlogin  ALIAS FOR $1;
     userpasswd   ALIAS FOR $2;
     row users%ROWTYPE;
  BEGIN

     FOR row IN SELECT user_id FROM users WHERE user_login = userlogin
AND user_passwd = userpasswd AND user_account = TRUE LOOP
       RETURN NEXT row;
     END LOOP;
   RETURN;
  END;
' LANGUAGE 'plpgsql';

----------------- CODE END --------------------------

Ycrux <ycrux@club-internet.fr> writes:
> # SELECT grantAccess('sara', 'sarapass');
> ERROR:  set-valued function called in context that cannot accept a set

You need to do "SELECT * FROM grantAccess(...)".  This is a plpgsql
implementation restriction that we'll probably try to fix someday,
although there's also a school of thought that says that set-returning
functions in the SELECT targetlist are a bad idea and should be phased
out.

            regards, tom lane

Hi All!
First of all, a great Thanks, your suggestions works fine.

I'll hope to enhance a little bit my understanding of SETOF return type.
I have now two problems.

1) I would like to return some columns from one table in PL/pgSQL function. What's in this case the correct return type
ofthe PL/pgSQL function. This is a pseudo-code for my first problem: 

--------------------------------------------------------------------
CREATE FUNCTION function_name (sometypes) RETURNS return_type AS $$
FOR some_type IN SELECT column1, column3 FROM tablename WHERE some_conditions
LOOP
          RETURN NEXT some_type;
END LOOP;
RETURN;
$$ LANGUAGE 'plpgsql' STABLE;
--------------------------------------------------------------------
What's return_type and some_type in this case?

2) The next problem is almost same as above. But now, I would like to return different columns from different tables.
What's in this case the correct return type of PL/pgSQL function.
This is a pseudo-code for my second problem:

--------------------------------------------------------------------
CREATE FUNCTION function_name (sometypes) RETURNS return_type AS $$
FOR some_type IN SELECT column1_table1, column17_table2 FROM table1, table2 WHERE some_conditions
LOOP
          RETURN NEXT some_type;
END LOOP;
RETURN;
$$ LANGUAGE 'plpgsql' STABLE;
--------------------------------------------------------------------

Thanks in advance
Younes



----Message d'origine----
>A: Ycrux <ycrux@club-internet.fr>
>Copie à: pgsql-general@postgresql.org
>Sujet: Re: [GENERAL] PL/pgSQL question
>Date: Thu, 09 Mar 2006 19:25:52 -0500
>De: Tom Lane <tgl@sss.pgh.pa.us>
>
>Ycrux <ycrux@club-internet.fr> writes:
>> # SELECT grantAccess('sara', 'sarapass');
>> ERROR:  set-valued function called in context that cannot accept a set
>
>You need to do "SELECT * FROM grantAccess(...)".  This is a plpgsql
>implementation restriction that we'll probably try to fix someday,
>although there's also a school of thought that says that set-returning
>functions in the SELECT targetlist are a bad idea and should be phased
>out.
>
>            regards, tom lane
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Have you searched our list archives?
>
>               http://archives.postgresql.org
>
>

ycrux@club-internet.fr wrote:
> Hi All!
> First of all, a great Thanks, your suggestions works fine.
>
> I'll hope to enhance a little bit my understanding of SETOF return type.
> I have now two problems.
>
> 1) I would like to return some columns from one table in PL/pgSQL function. What's in this case the correct return
typeof the PL/pgSQL function. This is a pseudo-code for my first problem: 
>
> --------------------------------------------------------------------
> CREATE FUNCTION function_name (sometypes) RETURNS return_type AS $$
> FOR some_type IN SELECT column1, column3 FROM tablename WHERE some_conditions
> LOOP
>           RETURN NEXT some_type;
> END LOOP;
> RETURN;
> $$ LANGUAGE 'plpgsql' STABLE;
> --------------------------------------------------------------------
> What's return_type and some_type in this case?

Depends on what column1,column3 are. See the manuals for CREATE TYPE.
If column1 was int4 and column3 was a date you'd do something like:
   CREATE TYPE return_type AS (
     a int4,
     b date
   );

some_type is a variable not a type definition, although you'd probably
define it to be of type "return_type".

Oh, and it should be ... RETURNS SETOF return_type

> 2) The next problem is almost same as above. But now, I would like to return different columns from different tables.
> What's in this case the correct return type of PL/pgSQL function.
> This is a pseudo-code for my second problem:
>
> --------------------------------------------------------------------
> CREATE FUNCTION function_name (sometypes) RETURNS return_type AS $$
> FOR some_type IN SELECT column1_table1, column17_table2 FROM table1, table2 WHERE some_conditions
> LOOP
>           RETURN NEXT some_type;
> END LOOP;
> RETURN;
> $$ LANGUAGE 'plpgsql' STABLE;

Same difference, but you would change your type definition.

--
   Richard Huxton
   Archonet Ltd