Re: function returning result set of varying column

2008/6/3 maria s <psmg01(at)gmail(dot)com>:
> Hi Pavel Stehule,
> Thanks for your reply.
>
> If I want to return a string and an array how should I do it?
> The problem is as I explained before.

postgres=# create or replace function foo(j integer, out a varchar,
out b varchar[]) as $$
begin a := 'kuku'; b := '{}';
for i in 1..j loop b := b || (a || i)::varchar; end loop;
end; $$ language plpgsql;
CREATE FUNCTION
Time: 4,819 ms
postgres=# select * from foo(3);
a | b
------+---------------------
kuku | {kuku1,kuku2,kuku3}
(1 row)

>
> I have 2 tables. For a single entry E1 in one table(t1), I have
> to fetch all the matching entries for E1 from the other
> table(t2), K1,..Kn, M1...Mn and finally the function should return E1,
> K1..Kn, M1...Mn.
postgres=# create table a(x integer);
CREATE TABLE
Time: 140,440 ms
postgres=# create table b(x integer, y integer);
CREATE TABLE
Time: 7,532 ms
postgres=# insert into a values(10),(20);
INSERT 0 2
Time: 4,065 ms
postgres=# insert into b values(10,1),(10,2),(10,3),(20,8),(20,7);
INSERT 0 5
Time: 2,711 ms

postgres=# select x, (select array(select y from b where b.x = a.x)) from a;
x | ?column?
----+----------
10 | {1,2,3}
20 | {8,7}
(2 rows)

other solution is using custom agg function
CREATE AGGREGATE array_accum (anyelement)
(
sfunc = array_append,
stype = anyarray,
initcond = '{}'
);
http://www.postgresql.org/docs/8.3/interactive/xaggr.html

postgres=# select x, array_accum(y) from b group by x;
x | array_accum
----+-------------
20 | {8,7}
10 | {1,2,3}
(2 rows)

maybe you don't need function and you need only view. I don't know:

create view someview as select x, array_accum(y) from b group by x;
postgres=# select * from someview ;
x | array_accum
----+-------------
20 | {8,7}
10 | {1,2,3}
(2 rows)

postgres=# create or replace function fx(integer, out varchar, out
varchar) as $$select x::varchar, (select array(select y from b where
b.x = a.x))::varchar
from a where a.x = $1
$$ language sql;
CREATE FUNCTION
Time: 5,111 ms
postgres=# select * from fx(10);
column1 | column2
---------+---------
10 | {1,2,3}
(1 row)

Regards
Pavel Stehule

>
> t1
> sample-id,
> samplename
>
> recs
> 1 c-01
> 2 c-02
>
> t2
> sampleid, property_name, property_value
>
> recs
> 1 , lps , 1
> 1, hr, 2
> 1, cd04, 1
>
> 2, lps, 1
> 2, hr, 5
>
>
> Could you please tell me how should I get this as string and array type of
> [][] that fetches propert_type and value array?
>
> select * from myfunction() as ("field1" text, "field2" text[][])
>
> Thanks a lot for your help,
> Maria
>
> On Tue, Jun 3, 2008 at 10:13 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
> wrote:
>>
>> 2008/6/3 maria s <psmg01(at)gmail(dot)com>:
>> > Hi Ivan,
>> > If I have to know the column names then I can't use the Functions.
>> > As I said before, the columns will vary. or As Pavel Stehule said
>> > I will use arrays.
>> >
>> > Is anyone can show an example of returning a record with string and
>> > array?
>>
>>
>> postgres=# create or replace function foo(int) returns text[] as
>> $$select array(select 'kuku' || i from generate_series(1,$1) g(i))$$
>> language sql;
>> CREATE FUNCTION
>> Time: 69,730 ms
>> postgres=# select foo(10);
>> foo
>> ----------------------------------------------------------------
>> {kuku1,kuku2,kuku3,kuku4,kuku5,kuku6,kuku7,kuku8,kuku9,kuku10}
>> (1 row)
>>
>> Time: 1,739 ms
>> postgres=# select foo(5);
>> foo
>> ---------------------------------
>> {kuku1,kuku2,kuku3,kuku4,kuku5}
>> (1 row)
>>
>> Time: 1,274 ms
>>
>> >
>> > Thanks,
>> > Maria
>> >
>> > On Tue, Jun 3, 2008 at 9:57 AM, Ivan Sergio Borgonovo
>> > <mail(at)webthatworks(dot)it>
>> > wrote:
>> >>
>> >> On Tue, 3 Jun 2008 09:41:27 -0400
>> >> "maria s" <psmg01(at)gmail(dot)com> wrote:
>> >>
>> >> > Thanks for all your replies.
>> >> >
>> >> > Actually I don't know the number of columns that I am going to
>> >> > return.
>> >> >
>> >> > I have 2 tables. For a single entry E1 in one table(t1), I have
>> >> > to fetch all the matching entries for E1 from the other
>> >> > table(t2), K1,..Kn. and finally the function should return E1,
>> >> > K1..Kn. So I don't know the number of columns that I am going to
>> >> > get.
>> >> >
>> >> > Is it possible to write a function that returns this kind of
>> >> > result?
>> >>
>> >> Up to my knowledge as Bart wrote in pl/pgsql you'll have to specify
>> >> somewhere the return type: in the function or in the select calling
>> >> the function.
>> >> If you use sql (not pl/pgsql) function you shouldn't be obliged to
>> >> specify the return type.
>> >> But I haven't written enough sql function to actually remember how
>> >> it works.
>> >>
>> >> If you post your tentative sql it could give us more clue.
>> >>
>> >> --
>> >> Ivan Sergio Borgonovo
>> >> http://www.webthatworks.it
>> >>
>> >>
>> >> --
>> >> Sent via pgsql-sql mailing list (pgsql-sql(at)postgresql(dot)org)
>> >> To make changes to your subscription:
>> >> http://www.postgresql.org/mailpref/pgsql-sql
>> >
>> >
>
>