Re: function returning result set of varying column - Mailing list pgsql-sql
| From | maria s |
|---|---|
| Subject | Re: function returning result set of varying column |
| Date | |
| Msg-id | d9d42a0f0806030806k4beec373h2758d9621fe4a900@mail.gmail.com Whole thread Raw |
| In response to | Re: function returning result set of varying column ("Pavel Stehule" <pavel.stehule@gmail.com>) |
| Responses |
Re: function returning result set of varying column
|
| List | 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
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.stehule@gmail.com> wrote:
2008/6/3 maria s <psmg01@gmail.com>:> Hi Ivan,postgres=# create or replace function foo(int) returns text[] as
> 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?
$$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
>
>