Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxx> wrote:
On 11/7/06, Alain Roger < raf.news@xxxxxxxxx> 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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux