From: Andy <frum@ar-sd.net>
To: pgsql-sql@postgresql.org
Subject: plpgsql record as parameter ???
Date: Wed, 18 Oct 2006 16:49:32 +0300
Message-ID: <001401c6f2bc$3f6c1860$0b00a8c0@mpsro.dom> (raw)
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: frum@ar-sd.net
Subject: Re: plpgsql record as parameter ???
In-Reply-To: <001401c6f2bc$3f6c1860$0b00a8c0@mpsro.dom>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox