plpgsql record as parameter ???

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.

Responses

Browse pgsql-sql by date

  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?