Re: FOR ... IN

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 //