From: | "Andy" <frum(at)ar-sd(dot)net> |
---|---|
To: | <pgsql-sql(at)postgresql(dot)org> |
Subject: | plpgsql record as parameter ??? |
Date: | 2006-10-18 13:49:32 |
Message-ID: | 001401c6f2bc$3f6c1860$0b00a8c0@mpsro.dom |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Hi, I have the following function:
CREATE OR REPLACE FUNCTION zahlavis_rech_list(int4, varchar(10)) RETURNS "varchar" AS
$BODY$
DECLARE
avis_id ALIAS FOR $1;
rech_type ALIAS FOR $2;
rech_list text;
sql text;
rec RECORD;
BEGIN
rech_list := '';
sql := 'SELECT '|| rech_type ||' as xx FROM rechnung WHERE id IN (SELECT id_rechnung FROM rechnung_zahlavis WHERE id_zahlavis IN (' || avis_id || '))';
FOR rec IN execute sql
loop
RAISE WARNING 'value = %', rec.xx ;
rech_list := rech_list || ',' || rec.xx;
end loop;
return substr(rech_list,2);
END
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
I want to give as a second parameter a column from the table. It works ONLY when I run the function for the first and only with that parameter.
For example:
select zahlavis_rech_list(1, 'nummer');
WARNING: value = 103670
WARNING: value = 103603
WARNING: value = 103345
WARNING: value = 103318
WARNING: value = 103882
WARNING: value = 103241
WARNING: value = 109124
Total query runtime: 16 ms.
Data retrieval runtime: 15 ms.
1 rows retrieved.
EXECUTION OK!
select zahlavis_rech_list(1, 'id');
WARNING: value = 504
ERROR: type of "rec.xx" does not match that when preparing the plan
CONTEXT: PL/pgSQL function "zahlavis_rech_list" line 14 at assignment
EXECUTION ERROR!
Both id, and nummer are columns from the table.
I tried different solutions but no result.
Help!!!! && regards,
Andy.
From | Date | Subject | |
---|---|---|---|
Next Message | Jim C. Nasby | 2006-10-18 18:43:47 | Re: [HACKERS] Bug? |
Previous Message | Andrew Dunstan | 2006-10-18 13:31:54 | Re: Bug? |