how can I returns a set of integer in a plpgsql function?

From: Yi Zhao <yi(dot)zhao(at)alibaba-inc(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: how can I returns a set of integer in a plpgsql function?
Date: 2009-01-15 07:37:57
Message-ID: 1232005077.3109.9.camel@localhost.localdomain
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-hackers

hi, all:
there is a function in postgresql contrib "int_arrgreagte":

CREATE OR REPLACE FUNCTION int_array_enum(int4[])
RETURNS setof integer
AS '$libdir/int_aggregate','int_enum'
LANGUAGE C IMMUTABLE STRICT;

I can use this function like this:
chry=# SELECT int_array_enum('{1,2}');
or
chry=# SELECT * from int_array_enum('{1,2}');
result:
int_array_enum
----------------
1
2

also, I can use it like this:
SELECT int_array_enum('{1,2}'), int_array_enum('{1,3}');
result:
int_array_enum | int_array_enum
----------------+----------------
1 | 1
2 | 3

I try to write my own function with the same return type in plpgsql:
create or replace function my_int_array_enum(state integer[]) returns
setof integer as $$
declare
i integer;
begin
for i in array_lower(state,1)..array_upper(state,1) loop
return next state[i];
end loop;
return;
end;
$$ language 'plpgsql' immutable;

but, when I use my function like this: test=# SELECT
my_int_array_enum('{1,2}');
I got the error said as below:
ERROR: set-valued function called in context that cannot accept a set
(I know, SELECT * from my_int_array_enum('{1,2}') is ok)

can anybody tell me the reason, thanks for any help:D

regards,

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Albe Laurenz 2009-01-15 07:41:46 Re: fire trigger for a row without update?
Previous Message Justin Pasher 2009-01-15 03:36:41 Autovacuum daemon terminated by signal 11

Browse pgsql-hackers by date

  From Date Subject
Next Message Albe Laurenz 2009-01-15 07:41:46 Re: fire trigger for a row without update?
Previous Message Heikki Linnakangas 2009-01-15 06:30:34 Re: Visibility map, partial vacuums