Re: function returning result set of varying column - Mailing list pgsql-sql

2008/6/3 maria s <psmg01@gmail.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.stehule@gmail.com>
> wrote:
>>
>> 2008/6/3 maria s <psmg01@gmail.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@webthatworks.it>
>> > wrote:
>> >>
>> >> On Tue, 3 Jun 2008 09:41:27 -0400
>> >> "maria s" <psmg01@gmail.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@postgresql.org)
>> >> To make changes to your subscription:
>> >> http://www.postgresql.org/mailpref/pgsql-sql
>> >
>> >
>
>