Re: plpgsql record as parameter ???

On 10/18/06, Andy <frum(at)ar-sd(dot)net> wrote:
>
> 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.
>

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
==================================================================