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
