Thread: plpgsql record as parameter ???
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
$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;
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
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.
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
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.
On 10/18/06, Andy <frum@ar-sd.net> wrote:
What do your tables look like? This is caused by a data type mismatch so I wonder if the columns nummer and id are different types.
--
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
http://codeelixir.com
==================================================================
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;
BEGINrech_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 = 109124Total query runtime: 16 ms.
Data retrieval runtime: 15 ms.
1 rows retrieved.EXECUTION OK!select zahlavis_rech_list(1, 'id');WARNING: value = 504ERROR: type of "rec.xx" does not match that when preparing the plan
CONTEXT: PL/pgSQL function "zahlavis_rech_list" line 14 at assignmentEXECUTION ERROR!Both id, and nummer are columns from the table.I tried different solutions but no result.Help!!!! && regards,Andy.
What do your tables look like? This is caused by a data type mismatch so I wonder if the columns nummer and id are different types.
--
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
http://codeelixir.com
==================================================================