Thread: FOR ... IN

Hi,

Before (in version 8.0.1), i did the following thing and it was working well...now (in version 8.1.4) it seems that it does not work anymore...
problem is with FOR rec IN loop...
So how can i tell "FOR all RECORDS from select * from articles, articletypes, department where ..." LOOP ... ?

thanks,
Al.


CREATE OR REPLACE FUNCTION "public"."SP_U_001" ("TypeOfArticle" varchar) RETURNS SETOF "public"."active_articles" AS
$body$
DECLARE
  TypeArt VARCHAR := $1;
    rec RECORD;
    res active_articles;
/**************************************/
BEGIN
                   
  FOR rec 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
        res.article_type := rec.articletypes.articletype_type;
        res.article_author := rec.articles.author;
        res.department_owner := rec.department.department_name;
        res.department_picture := rec.department.department_picture;
        res.article_title := rec.articles.title;
        res.article_content := rec.articles.content;
        res.date_creation := rec.articles.creation_date;
        res.date_start := rec.articles.validity_period_start;
        res.date_end := rec.articles.validity_period_end;
      RETURN NEXT res;
    END LOOP;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

Alain Roger wrote:
> Hi,
>
> Before (in version 8.0.1), i did the following thing and it was working
> well...now (in version 8.1.4) it seems that it does not work anymore...
> problem is with FOR rec IN loop...

Could you elaborate on "does not work"?

> So how can i tell "FOR all RECORDS from select * from articles,
> articletypes, department where ..." LOOP ... ?

Well, seeing as this is apparently the same function you posted about
earlier, I suspect it's the same problem with the DECLARE block.

I rather doubt that FOR .. IN broke between releases.

> CREATE OR REPLACE FUNCTION "public"."SP_U_001" ("TypeOfArticle" varchar)
> RETURNS SETOF "public"."active_articles" AS
> $body$
> DECLARE
>  TypeArt VARCHAR := $1;
>    rec RECORD;
>    res active_articles;
> /**************************************/
> BEGIN


--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
    7500 AK Enschede

// Integrate Your World //

2006/11/6, Alain Roger <raf.news@gmail.com>:

   Never forgot of return before "END" procedure;

--
William Leite Araújo

2006/11/7, Alain Roger <raf.news@gmail.com>:

--
William Leite Araújo

Hi William,

i've read that RETURN should be used when function does not return a set. in my case, i return a set. so i can not write twice return.
Here is my latest version of my function.

-- Function: SP_U_001(typeofarticle varchar)

-- DROP FUNCTION SP_U_001(typeofarticle varchar);

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

and this is the error message i get :

ERROR:  schema "myrec" does not exist
CONTEXT:  SQL statement "SELECT  myrec.articletypes.articletype_type "
PL/pgSQL function "sp_u_001" line 17 at assignment

line 17 consists of WHERE close if you count comments, if not, i consists of last line of my SELECT command ==> AND articles.validity_period_end > now()



--------------------

Alain Roger wrote:
> Hi William,

> -- Function: SP_U_001(typeofarticle varchar)
>
> -- DROP FUNCTION SP_U_001(typeofarticle varchar);
>
> CREATE OR REPLACE FUNCTION SP_U_001(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_type := myrec.articletype_type;

>        res.article_author := myrec.articles.author;
>        res.department_owner := myrec.department.department_name;
         res.department_owner := myrec.department_name;

>        res.department_picture := myrec.department.department_picture;
         res.department_picture := myrec.department_picture;

etcetera.

> and this is the error message i get :
>
> ERROR:  schema "myrec" does not exist

The query results don't contain information about the tables they came
from, so inserting a table-name in your record syntax makes myrec be
interpreted as a schema instead of a variable.

> CONTEXT:  SQL statement "SELECT  myrec.articletypes.articletype_type"
> PL/pgSQL function "sp_u_001" line 17 at assignment
>
> line 17 consists of WHERE close if you count comments, if not, i
> consists of
> last line of my SELECT command ==> AND articles.validity_period_end > now()

Line 17 is your first (faulty) assignment from myrec. Line 1 is the line
containing 'DECLARE'.

--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
    7500 AK Enschede

// Integrate Your World //