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 ?