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