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(at)magproductions(dot)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 //