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

On 11/7/06, Alain Roger <raf(dot)news(at)gmail(dot)com> wrote:
> 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"

you are using composite types right? you have to add parenthesis to
disambiguate this case:

http://www.postgresql.org/docs/8.1/interactive/rowtypes.html#AEN5789

res.article_title := myrec.(articles).title;

sorry i missed that the first time out.

merlin