Skip site navigation (1) Skip section navigation (2)

Re: function returning result set of varying column

From: "maria s" <psmg01(at)gmail(dot)com>
To: "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com>
Cc: "Ivan Sergio Borgonovo" <mail(at)webthatworks(dot)it>, pgsql-sql(at)postgresql(dot)org
Subject: Re: function returning result set of varying column
Date: 2008-06-03 15:06:02
Message-ID: d9d42a0f0806030806k4beec373h2758d9621fe4a900@mail.gmail.com (view raw or flat)
Thread:
Lists: 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
> >
> >
>

In response to

Responses

pgsql-sql by date

Next:From: Ivan Sergio BorgonovoDate: 2008-06-03 15:17:48
Subject: Re: function returning result set of varying column
Previous:From: Pavel StehuleDate: 2008-06-03 14:13:28
Subject: Re: function returning result set of varying column

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group