Re: function returning result set of varying column

Hi friends,
I am very new to plsql.

I have to write a function that quries few tables and returns a resultset
of varying column.

In that case I cannot predefine the table with column.
If I use RETURNS SETOF then I should know the number of columns and its
type?!

Is there anyway to return a resultset with any number of column?

Thanks for your help.

-maria

Hi Maria,
Try something like
CREATE OR REPLACE FUNCTION myfunction() RETURNS SETOF RECORD AS
$body$
DECLARE
rec record;
BEGIN
FOR rec IN (
SELECT * FROM sometable)
LOOP
RETURN NEXT rec;
END LOOP;
RETURN;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE;

As you can see, the number and type of the output fields only depends on whatever table you query in the FOR loop.
It's not magic though. It just postpones defining the number and type of the output fields until querying the function.
You will have to define the output fields when querying your function, like
select * from myfunction() as ("field1" integer, "field2" text, ...)

>>> "maria s" <psmg01(at)gmail(dot)com> 2008-06-02 22:40 >>>
Hi friends,
I am very new to plsql.

I have to write a function that quries few tables and returns a resultset of varying column.

In that case I cannot predefine the table with column.
If I use RETURNS SETOF then I should know the number of columns and its type?!

Is there anyway to return a resultset with any number of column?

Thanks for your help.

-maria

Hi Friends,
Thanks for all your for the reply.

I tried the function and when I execute it using
select * from myfunction()
it says
ERROR: a column definition list is required for functions returning
"record"

Could you please help me to fix this error?

Thanks so much for your help.

-maria

On Tue, Jun 3, 2008 at 3:06 AM, Bart Degryse <Bart(dot)Degryse(at)indicator(dot)be>
wrote:

> Hi Maria,
> Try something like
> CREATE OR REPLACE FUNCTION myfunction() RETURNS SETOF RECORD AS
> $body$
> DECLARE
> rec record;
> BEGIN
> FOR rec IN (
> SELECT * FROM sometable)
> LOOP
> RETURN NEXT rec;
> END LOOP;
> RETURN;
> END;
> $body$
> LANGUAGE 'plpgsql' VOLATILE;
>
> As you can see, the number and type of the output fields only depends on
> whatever table you query in the FOR loop.
> It's not magic though. It just postpones defining the number and type of
> the output fields until querying the function.
> You will have to define the output fields when querying your function, like
> select * from myfunction() as ("field1" integer, "field2" text, ...)
>
> >>> "maria s" <psmg01(at)gmail(dot)com> 2008-06-02 22:40 >>>
>
> Hi friends,
> I am very new to plsql.
>
> I have to write a function that quries few tables and returns a resultset
> of varying column.
>
> In that case I cannot predefine the table with column.
> If I use RETURNS SETOF then I should know the number of columns and its
> type?!
>
> Is there anyway to return a resultset with any number of column?
>
> Thanks for your help.
>
> -maria
>

As I wrote before you will have to define your fields when querying the function,
eg. select * from myfunction() as ("field1" integer, "field2" text, ...)
So suppose you have a table like this
CREATE TABLE sometable (
"id" serial,
"sometextfield" text,
"aninteger" int,
"andavarchar" varchar(20)
);
you would have to query the function below like this:
select * from myfunction() as ("id" integer, "sometextfield" text, "aninteger" integer, "andavarchar" varchar);

You are however not obliged to use the same fieldnames as defined in the table definition, so this will work too:
select * from myfunction() as ("myid" integer, "textfield" text, "myint" integer, "varfield" varchar);

Automatic type conversion will work too, so this is ok too
select * from myfunction() as ("myid" integer, "textfield" text, "myint" integer, "varfield" text);

Now suppose you change the selecte statement in myfunction to
SELECT sometextfield, andavarchar FROM sometable WHERE id > 5

Now myfunction will not return 4 fields but only 2 and thus you would query myfunction like this
select * from myfunction() as ("sometextfield" text, "andavarchar" varchar);
or
select * from myfunction() as ("textfield" text, "varfield" varchar);
or
select * from myfunction() as ("textfield" text, "varfield" text);

So defining your function as RETURNS SETOF RECORD only postpones the moment you define your output fields.

Either you define your output fields when creating your function, or (like in the above example) when querying your function.


>>> "maria s" <psmg01(at)gmail(dot)com> 2008-06-03 15:12 >>>
Hi Bart,
I will not know the number of fields. Because it will vary .
Is there any solution for this?

Thanks,
Maria

>>> "maria s" <psmg01(at)gmail(dot)com> 2008-06-03 15:01 >>>
Hi Friends,
Thanks for all your for the reply.

I tried the function and when I execute it using
select * from myfunction()
it says
ERROR: a column definition list is required for functions returning "record"

Could you please help me to fix this error?

Thanks so much for your help.

-maria

On Tue, Jun 3, 2008 at 3:06 AM, Bart Degryse <Bart(dot)Degryse(at)indicator(dot)be> wrote:

Hi Maria,
Try something like
CREATE OR REPLACE FUNCTION myfunction() RETURNS SETOF RECORD AS
$body$
DECLARE
rec record;
BEGIN
FOR rec IN (
SELECT * FROM sometable)
LOOP
RETURN NEXT rec;
END LOOP;
RETURN;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE;

As you can see, the number and type of the output fields only depends on whatever table you query in the FOR loop.
It's not magic though. It just postpones defining the number and type of the output fields until querying the function.
You will have to define the output fields when querying your function, like
select * from myfunction() as ("field1" integer, "field2" text, ...)

>>> "maria s" <psmg01(at)gmail(dot)com> 2008-06-02 22:40 >>>

Hi friends,
I am very new to plsql.

I have to write a function that quries few tables and returns a resultset of varying column.

In that case I cannot predefine the table with column.
If I use RETURNS SETOF then I should know the number of columns and its type?!

Is there anyway to return a resultset with any number of column?

Thanks for your help.

-maria

On Tue, 3 Jun 2008 09:01:02 -0400
"maria s" <psmg01(at)gmail(dot)com> wrote:

> Hi Friends,
> Thanks for all your for the reply.
>
> I tried the function and when I execute it using
> select * from myfunction()
> it says
> ERROR: a column definition list is required for functions
> returning "record"
>
> Could you please help me to fix this error?
>
> Thanks so much for your help.

you can specify the returned types in each statement that call your
function or you can specify the returned type in the function itself.

CREATE OR REPLACE FUNCTION myfunction(out col1 int, out col2
varchar(32), out ...)
RETURNS
SETOF
RECORD
AS
$body$
DECLARE
rec record;
BEGIN
FOR rec IN (
SELECT * FROM sometable)
LOOP
col1:=rec.col1;
col2:=rec.col2;
-- col3:=...;
RETURN NEXT;
END LOOP;
RETURN;
END;
$body$

> > CREATE OR REPLACE FUNCTION myfunction() RETURNS SETOF RECORD AS
> > $body$
> > DECLARE
> > rec record;
> > BEGIN
> > FOR rec IN (
> > SELECT * FROM sometable)
> > LOOP
> > RETURN NEXT rec;
> > END LOOP;
> > RETURN;
> > END;
> > $body$

--
Ivan Sergio Borgonovo
http://www.webthatworks.it

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?

Please help.

Thanks,
maria

On Tue, Jun 3, 2008 at 9:28 AM, Ivan Sergio Borgonovo <mail(at)webthatworks(dot)it>
wrote:

> On Tue, 3 Jun 2008 09:01:02 -0400
> "maria s" <psmg01(at)gmail(dot)com> wrote:
>
> > Hi Friends,
> > Thanks for all your for the reply.
> >
> > I tried the function and when I execute it using
> > select * from myfunction()
> > it says
> > ERROR: a column definition list is required for functions
> > returning "record"
> >
> > Could you please help me to fix this error?
> >
> > Thanks so much for your help.
>
> you can specify the returned types in each statement that call your
> function or you can specify the returned type in the function itself.
>
> CREATE OR REPLACE FUNCTION myfunction(out col1 int, out col2
> varchar(32), out ...)
> RETURNS
> SETOF
> RECORD
> AS
> $body$
> DECLARE
> rec record;
> BEGIN
> FOR rec IN (
> SELECT * FROM sometable)
> LOOP
> col1:=rec.col1;
> col2:=rec.col2;
> -- col3:=...;
> RETURN NEXT;
> END LOOP;
> RETURN;
> END;
> $body$
>
> > > CREATE OR REPLACE FUNCTION myfunction() RETURNS SETOF RECORD AS
> > > $body$
> > > DECLARE
> > > rec record;
> > > BEGIN
> > > FOR rec IN (
> > > SELECT * FROM sometable)
> > > LOOP
> > > RETURN NEXT rec;
> > > END LOOP;
> > > RETURN;
> > > END;
> > > $body$
>
> --
> 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
>

2008/6/3 maria s <psmg01(at)gmail(dot)com>:
> 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?

no, it's not possible. You have to know number and types of result
columns before function's execution.

try to use arrays.

Regards
Pavel Stehule
>
> Please help.
>
> Thanks,
> maria
>
> On Tue, Jun 3, 2008 at 9:28 AM, Ivan Sergio Borgonovo <mail(at)webthatworks(dot)it>
> wrote:
>>
>> On Tue, 3 Jun 2008 09:01:02 -0400
>> "maria s" <psmg01(at)gmail(dot)com> wrote:
>>
>> > Hi Friends,
>> > Thanks for all your for the reply.
>> >
>> > I tried the function and when I execute it using
>> > select * from myfunction()
>> > it says
>> > ERROR: a column definition list is required for functions
>> > returning "record"
>> >
>> > Could you please help me to fix this error?
>> >
>> > Thanks so much for your help.
>>
>> you can specify the returned types in each statement that call your
>> function or you can specify the returned type in the function itself.
>>
>> CREATE OR REPLACE FUNCTION myfunction(out col1 int, out col2
>> varchar(32), out ...)
>> RETURNS
>> SETOF
>> RECORD
>> AS
>> $body$
>> DECLARE
>> rec record;
>> BEGIN
>> FOR rec IN (
>> SELECT * FROM sometable)
>> LOOP
>> col1:=rec.col1;
>> col2:=rec.col2;
>> -- col3:=...;
>> RETURN NEXT;
>> END LOOP;
>> RETURN;
>> END;
>> $body$
>>
>> > > CREATE OR REPLACE FUNCTION myfunction() RETURNS SETOF RECORD AS
>> > > $body$
>> > > DECLARE
>> > > rec record;
>> > > BEGIN
>> > > FOR rec IN (
>> > > SELECT * FROM sometable)
>> > > LOOP
>> > > RETURN NEXT rec;
>> > > END LOOP;
>> > > RETURN;
>> > > END;
>> > > $body$
>>
>> --
>> 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
>
>

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

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?

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
>

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

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.

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.

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

On Tue, 3 Jun 2008 10:06:45 -0400
"maria s" <psmg01(at)gmail(dot)com> wrote:

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

What I meant was that with "pure" sql functions you shouldn't have
that constraint...

wrapping a select in a function

create or replace function fsql() as
$$
begin
select * from mytable;
end;
$$ language sql;

should work.

SQL functions are less flexible than pg/plsql but maybe you can live
with it.
SQL functions still support conditionals etc... you'd have to see if
what SQL functions offer is enough for your need.

--
Ivan Sergio Borgonovo
http://www.webthatworks.it

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