Re: FOR ... IN

Hi William,

i've read that RETURN should be used when function does not return a set. in
my case, i return a set. so i can not write twice return.
Here is my latest version of my function.

-- Function: SP_U_001(typeofarticle varchar)

-- DROP FUNCTION SP_U_001(typeofarticle varchar);

CREATE OR REPLACE FUNCTION SP_U_001(VARCHAR)
RETURNS SETOF active_articles AS
$BODY$
DECLARE
myrec RECORD;
res active_articles;
/**************************************/
BEGIN

FOR myrec IN
select *
from articles, articletypes, department
where
articletypes.articletype_type = $1
AND articles.articletype_id = articletypes.articletype_id
AND articles.department_id = department.department_id
AND articles.validity_period_end > now()
LOOP
IF (myrec IS NOT NULL) THEN
res.article_type := myrec.articletypes.articletype_type;
res.article_author := myrec.articles.author;
res.department_owner := myrec.department.department_name;
res.department_picture := myrec.department.department_picture;
res.article_title := myrec.articles.title;
res.article_content := myrec.articles.content;
res.date_creation := myrec.articles.creation_date;
res.date_start := myrec.articles.validity_period_start;
res.date_end := myrec.articles.validity_period_end;
END IF;
RETURN NEXT res;
END LOOP;
RETURN;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION SP_U_001(VARCHAR) OWNER TO immensesk;
GRANT EXECUTE ON FUNCTION SP_U_001(VARCHAR) TO immensesk;

and this is the error message i get :

ERROR: schema "myrec" does not exist
CONTEXT: SQL statement "SELECT myrec.articletypes.articletype_type"
PL/pgSQL function "sp_u_001" line 17 at assignment

line 17 consists of WHERE close if you count comments, if not, i consists of
last line of my SELECT command ==> AND articles.validity_period_end > now()

--------------------

On 11/7/06, William Leite Araújo <william(dot)bh(at)gmail(dot)com> wrote:
>
> 2006/11/7, Alain Roger <raf(dot)news(at)gmail(dot)com>:
> >
> > but there is already a RETURN NEXT res;
> > so what will be the point of this RETURN after the END LOOP; ?
>
>
> http://www.postgresql.org/docs/8.1/interactive/plpgsql-control-structures.html
>
>
>
>
>
> --
> William Leite Araújo
>