From: Aaron Bono <postgresql@aranya.com>
To: Andy <frum@ar-sd.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: plpgsql record as parameter ???
Date: Thu, 26 Oct 2006 17:57:40 -0500
Message-ID: <bf05e51c0610261557i2bec6734r5644caf561b332f4@mail.gmail.com> (raw)
In-Reply-To: <001401c6f2bc$3f6c1860$0b00a8c0@mpsro.dom>
References: <001401c6f2bc$3f6c1860$0b00a8c0@mpsro.dom>
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.comhttp://codeelixir.com
==================================================================
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: postgresql@aranya.com, frum@ar-sd.net
Subject: Re: plpgsql record as parameter ???
In-Reply-To: <bf05e51c0610261557i2bec6734r5644caf561b332f4@mail.gmail.com>
* 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