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.