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 <[EMAIL PROTECTED]> wrote: > 2008/6/3 maria s <[EMAIL PROTECTED]>: > > 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 < > [EMAIL PROTECTED]> > > wrote: > >> > >> On Tue, 3 Jun 2008 09:41:27 -0400 > >> "maria s" <[EMAIL PROTECTED]> 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 > > > > >