FOR ... IN

Alain Roger wrote:

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...

Could you elaborate on "does not work"?

So how can i tell "FOR all RECORDS from select * from articles,
articletypes, department where ..." LOOP ... ?

Well, seeing as this is apparently the same function you posted about
earlier, I suspect it's the same problem with the DECLARE block.

I rather doubt that FOR .. IN broke between releases.

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

--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede

// Integrate Your World //

2006/11/6, Alain Roger <raf.news@gmail.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

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()

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

Alain Roger wrote:

Hi William,

-- 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_type := myrec.articletype_type;

res.article_author := myrec.articles.author;
res.department_owner := myrec.department.department_name;

res.department_owner := myrec.department_name;

res.department_picture := myrec.department.department_picture;

res.department_picture := myrec.department_picture;

etcetera.

and this is the error message i get :

ERROR: schema "myrec" does not exist

The query results don't contain information about the tables they came
from, so inserting a table-name in your record syntax makes myrec be
interpreted as a schema instead of a variable.

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()

Line 17 is your first (faulty) assignment from myrec. Line 1 is the line
containing 'DECLARE'.

--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede

// Integrate Your World //