I'm lost :-( with FOR...IN

From: "Alain Roger" <raf(dot)news(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: I'm lost :-( with FOR...IN
Date: 2006-11-07 19:09:56
Message-ID: 75645bbb0611071109m2176c7dam2a9567e8b20438d9@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

I' still with my stored procedure :

-- Function: SP_U_001(typeofarticle varchar)

-- DROP FUNCTION SP_U_001(typeofarticle varchar);

CREATE OR REPLACE FUNCTION SP_U_001(IN typeofarticles 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;

whatever, i do the argument VARCHAR will be stored in double quote as
"varchar" when i check via pgAdmin GUI. Even if in command line it looks
like above.

I still have the same error message on "myrec" :
ERROR: schema "myrec" does not exist
CONTEXT: SQL statement "SELECT myrec.articletypes.articletype_type"

i do not understand as there is quite the same example in postgreSQl
8.1.4documentation on page 623-624 about "Looping Through Query
Results".
so where am i wrong ?

Al.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Jeff Davis 2006-11-07 19:15:38 WAL ends before end time of backup dump
Previous Message Andrew Sullivan 2006-11-07 18:58:57 Re: per-row security