Re: FOR ... IN

2006/11/6, Alain Roger <raf(dot)news(at)gmail(dot)com>:
>
> Hi,
>
> Before (in version 8.0.1), i did the following thing and it was working
> well...now (in version 8.1.4) it seems that it does not work anymore...
> problem is with FOR rec IN loop...
> So how can i tell "FOR all RECORDS from select * from articles,
> articletypes, department where ..." LOOP ... ?
>
> thanks,
> Al.
>
>
> CREATE OR REPLACE FUNCTION "public"."SP_U_001" ("TypeOfArticle" varchar)
> RETURNS SETOF "public"."active_articles" AS
> $body$
> DECLARE
> TypeArt VARCHAR := $1;
> rec RECORD;
> res active_articles;
> /**************************************/
> BEGIN
>
> FOR rec 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
> res.article_type := rec.articletypes.articletype_type;
> res.article_author := rec.articles.author;
> res.department_owner := rec.department.department_name;
> res.department_picture := rec.department.department_picture;
> res.article_title := rec.articles.title;
> res.article_content := rec.articles.content;
> res.date_creation := rec.articles.creation_date;
> res.date_start := rec.articles.validity_period_start;
> res.date_end := rec.articles.validity_period_end;
> RETURN NEXT res;
> END LOOP;

RETURN;

END;
> $body$
> LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

Never forgot of return before "END" procedure;

--
William Leite Araújo