Postgres Pro
Downloads
Home   >   mailing lists

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

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

From
"Alain Roger"
Date:
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.4 documentation on page 623-624 about "Looping Through Query Results".
so where am i wrong ?

Al.

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

From
"Merlin Moncure"
Date:
On 11/7/06, Alain Roger <raf.news@gmail.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

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

From
"Alain Roger"
Date:
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@gmail.com> wrote:
On 11/7/06, Alain Roger < raf.news@gmail.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

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

From
Stephan Szabo
Date:
On Tue, 7 Nov 2006, Alain Roger 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;

I don't think the column names are going to keep their originating table
name inside the record, so the field probably needs
to be referred to as myrec.articletype_type not
myrec.articletypes.articletype_type.

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

From
"Alain Roger"
Date:
i already tried this possibility and i've got :
ERROR:  set-valued function called in context that cannot accept a set
CONTEXT:  PL/pgSQL function "sp_u_001" line 26 at return next

:-(

On 11/7/06, Stephan Szabo <sszabo@megazone.bigpanda.com> wrote:

On Tue, 7 Nov 2006, Alain Roger 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;

I don't think the column names are going to keep their originating table
name inside the record, so the field probably needs
to be referred to as myrec.articletype_type not
myrec.articletypes.articletype_type.


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

From
"Alain Roger"
Date:
Ok guys...i found the stupid problem :-((

everytime that i call my stored procedure, i did like that : select sp_u_001('action'); instead of  select * from sp_u_001('action');
thanks to all of you for your tips, they helped me to understand composite.

one last question : how can i test if myrec composite has some records ?
i was thinking about == > select count(*) from (myrec); but i'm not sure about the logic of this command.

Al.

On 11/7/06, Alain Roger <raf.news@gmail.com> wrote:
i already tried this possibility and i've got :
ERROR:  set-valued function called in context that cannot accept a set
CONTEXT:  PL/pgSQL function "sp_u_001" line 26 at return next

:-(


On 11/7/06, Stephan Szabo <sszabo@megazone.bigpanda.com> wrote:

On Tue, 7 Nov 2006, Alain Roger 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;

I don't think the column names are going to keep their originating table
name inside the record, so the field probably needs
to be referred to as myrec.articletype_type not
myrec.articletypes.articletype_type.