From frum@ar-sd.net Wed Oct 18 14:07:53 2006 Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id BF9409FB2E0 for ; Wed, 18 Oct 2006 11:07:53 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 16480-01-4 for ; Wed, 18 Oct 2006 11:07:48 -0300 (ADT) X-Greylist: delayed 00:19:00.142804 by SQLgrey- Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id 1CD159FB515 for ; Wed, 18 Oct 2006 11:07:28 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.3 Received: from ar-sd.net (mail.ar-sd.net [81.196.35.136]) by svr4.postgresql.org (Postfix) with ESMTP id CBA6D5AF910 for ; Wed, 18 Oct 2006 13:48:27 +0000 (GMT) Received: from localhost (localhost [127.0.0.1]) by ar-sd.net (Postfix) with ESMTP id 84E3934089 for ; Wed, 18 Oct 2006 16:46:14 +0300 (EEST) Received: from ar-sd.net ([127.0.0.1]) by localhost (ar-sd.net [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 28162-08 for ; Wed, 18 Oct 2006 16:46:07 +0300 (EEST) Received: from forge (forge.mpsro.dom [192.168.0.11]) by ar-sd.net (Postfix) with SMTP id 0090833FF2 for ; Wed, 18 Oct 2006 16:46:06 +0300 (EEST) Message-ID: <001401c6f2bc$3f6c1860$0b00a8c0@mpsro.dom> From: "Andy" To: Subject: plpgsql record as parameter ??? Date: Wed, 18 Oct 2006 16:49:32 +0300 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0011_01C6F2D5.629733B0" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2900.2869 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2962 X-Virus-Scanned: by amavisd-new at ar-sd.net X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.135 tagged_above=0 required=5 tests=HTML_50_60, HTML_MESSAGE, UPPERCASE_25_50 X-Spam-Level: X-Archive-Number: 200610/171 X-Sequence-Number: 26560 This is a multi-part message in MIME format. ------=_NextPart_000_0011_01C6F2D5.629733B0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable 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 :=3D ''; sql :=3D '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 =3D %', rec.xx ; rech_list :=3D rech_list || ',' || rec.xx; end loop; return substr(rech_list,2);=20 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 =3D 103670 WARNING: value =3D 103603 WARNING: value =3D 103345 WARNING: value =3D 103318 WARNING: value =3D 103882 WARNING: value =3D 103241 WARNING: value =3D 109124 Total query runtime: 16 ms. Data retrieval runtime: 15 ms. 1 rows retrieved. EXECUTION OK! select zahlavis_rech_list(1, 'id'); WARNING: value =3D 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.=20 I tried different solutions but no result.=20 Help!!!! && regards,=20 Andy. ------=_NextPart_000_0011_01C6F2D5.629733B0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

Hi, I have the following = function:

CREATE OR REPLACE FUNCTION = zahlavis_rech_list(int4,=20 varchar(10)) RETURNS "varchar" AS
$BODY$
DECLARE
avis_id ALIAS = FOR=20 $1;
rech_type ALIAS FOR $2;
rech_list text;
sql text;
rec=20 RECORD;
BEGIN

rech_list :=3D '';
sql :=3D 'SELECT = '|| rech_type=20 ||' as xx FROM rechnung WHERE id IN (SELECT id_rechnung FROM = rechnung_zahlavis=20 WHERE id_zahlavis IN (' || avis_id || '))';
FOR rec IN execute=20 sql
loop
RAISE WARNING 'value =3D %', rec.xx = ;
=20 rech_list :=3D rech_list || ',' || rec.xx;
end loop;
return=20 substr(rech_list,2);
END
$BODY$
LANGUAGE 'plpgsql'=20 VOLATILE;

I want to give as a second parameter a = column from=20 the table. It works ONLY when I run the function for the first and only = with=20 that parameter.

For example:

select zahlavis_rech_list(1,=20 'nummer');

WARNING: value =3D = 103670
WARNING: =20 value =3D 103603
WARNING: value =3D 103345
WARNING: = value =3D=20 103318
WARNING: value =3D 103882
WARNING: value =3D=20 103241
WARNING: value =3D 109124

Total query runtime: 16 ms.
Data = retrieval=20 runtime: 15 ms.
1 rows retrieved.

EXECUTION OK!

select zahlavis_rech_list(1, = 'id');

WARNING: value =3D = 504

ERROR: type of "rec.xx" does not = match that=20 when preparing the plan
CONTEXT: PL/pgSQL function = "zahlavis_rech_list"=20 line 14 at assignment

EXECUTION ERROR!

Both id, and nummer are columns from = the table.=20

I tried different solutions but no = result.=20

Help!!!! && regards, =

Andy.

------=_NextPart_000_0011_01C6F2D5.629733B0-- From postgresql@aranya.com Thu Oct 26 22:58:26 2006 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--