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; END; $body$ LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;