pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

plpgsql record as parameter ???
2+ messages / 2 participants
[nested] [flat]

* plpgsql record as parameter ???
@ 2006-10-18 13:49 Andy <frum@ar-sd.net>
  2006-10-26 22:57 ` Re: plpgsql record as parameter ??? Aaron Bono <postgresql@aranya.com>
  0 siblings, 1 reply; 2+ messages in thread

From: Andy @ 2006-10-18 13:49 UTC (permalink / raw)
  To: pgsql-sql

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.

^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: plpgsql record as parameter ???
  2006-10-18 13:49 plpgsql record as parameter ??? Andy <frum@ar-sd.net>
@ 2006-10-26 22:57 ` Aaron Bono <postgresql@aranya.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Aaron Bono @ 2006-10-26 22:57 UTC (permalink / raw)
  To: Andy <frum@ar-sd.net>; +Cc: pgsql-sql

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

^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2006-10-26 22:57 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-10-18 13:49 plpgsql record as parameter ??? Andy <frum@ar-sd.net>
2006-10-26 22:57 ` Aaron Bono <postgresql@aranya.com>

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