Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id B6C1B9F9F7C for ; Thu, 26 Oct 2006 19:58:26 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 74039-01 for ; Thu, 26 Oct 2006 22:57:45 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.173]) by postgresql.org (Postfix) with ESMTP id D84CD9FB3B4 for ; Thu, 26 Oct 2006 19:57:42 -0300 (ADT) Received: by ug-out-1314.google.com with SMTP id 30so460278ugc for ; Thu, 26 Oct 2006 15:57:41 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:references:x-google-sender-auth; b=kFLxZfaWCHmitO0HrDL7MHZkeV80crKDq72izy8ObbvfffJPcOLYeyBSI3sMZ8y+23qOc4SWdOdOj8yNay+KH1UltneKi/CcJO3QFzyN8he9lc3Vnda3pAbGVHjWMBGjMk78PlPqYWaPt4WaMLSv8rFzKHrvmVJHJf0C+EHZrhY= Received: by 10.78.157.8 with SMTP id f8mr3361594hue; Thu, 26 Oct 2006 15:57:40 -0700 (PDT) Received: by 10.78.136.6 with HTTP; Thu, 26 Oct 2006 15:57:40 -0700 (PDT) Message-ID: Date: Thu, 26 Oct 2006 17:57:40 -0500 From: "Aaron Bono" To: Andy Subject: Re: plpgsql record as parameter ??? Cc: pgsql-sql@postgresql.org In-Reply-To: <001401c6f2bc$3f6c1860$0b00a8c0@mpsro.dom> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_33213_12531004.1161903460562" References: <001401c6f2bc$3f6c1860$0b00a8c0@mpsro.dom> X-Google-Sender-Auth: f5445f03aca828b3 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.334 tagged_above=0 required=5 tests=DNS_FROM_RFC_ABUSE, HTML_50_60, HTML_MESSAGE, SPF_PASS X-Spam-Level: X-Archive-Number: 200610/211 X-Sequence-Number: 26600 ------=_Part_33213_12531004.1161903460562 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline On 10/18/06, Andy 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 ================================================================== ------=_Part_33213_12531004.1161903460562 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline On 10/18/06, Andy <frum@ar-sd.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
================================================================== ------=_Part_33213_12531004.1161903460562--