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

If i do what you wrote, i can not create the function into my DB.
error on 1st (

On 11/7/06, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
>
> 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
>