Re: FOR ... IN - Mailing list pgsql-general

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()



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