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