Re: obtuse plpgsql function needs - Mailing list pgsql-sql

How will you know in your function what the field
names are (you won't) and how many fields to concat
unless the function would only work on a fixed number
of fields?

If it only works on a fixed number of fields,
you still have:myconcat( text, text, text, text )

called by select f, myconcat( f,f1,f2,f3) from t1;
andselect f, myconcat( f,f4,f5,f6) from t2;

The cost is typing in the param list.

For a variable length record it is trickier.
You can do it in C, of course.

The key pieces needed to do this are:* Ability to pass a generic RECORD to a function.    This *might* be in 7.4 but
I'mnot sure.    myconcat( t1 ); or possibly myconcat (t1.*);* Ability to know the number of columns in the RECORD    A
pg_catalogquery* Ability to access the columns by order in a loop    AFAIK you have to access the columns by name.
 

If you can work through those issues, then
you'll have it.  The pieces are available in
several areas, the generic types and languages
like plpython and plperl which may be able to
loop through a generic tuple, if they could input
a tuple.

I will hang onto this problem and if either of
us finds a solution, I'd like to publish it in
general bits.

elein



On Wed, Jul 23, 2003 at 09:06:49AM -0400, Robert Treat wrote:
> On Tue, 2003-07-22 at 19:33, elein wrote:
> > You'll need to pass the values down to your
> > concat function (which I suggest you don't call concat)
> > and have it return a text type.
> > 
> > What exactly is your problem?  I must be missing something.
> > 
> 
> The problem is that I need the function to be generic so that I don't
> have to pass the values down to the function, it just grabs the values
> automagically based on the table it's being called against.
> 
> Robert Treat 
> 
> > elein
> > 
> > On Tue, Jul 22, 2003 at 06:31:52PM -0400, Robert Treat wrote:
> > > given 
> > > 
> > > create table t1 (f,f1,f2,f3);
> > > create table t2 (f,f4,f5,f6);
> > > 
> > > i'm trying to create a function concat() that does something like:
> > > 
> > > select f,concat() as info from t1;
> > > 
> > > which returns a result set equivalent to:
> > > select f,('f1:' || f1 || '- f2:' || f2 || '- f3:' || f3) as x from t1;
> > > 
> > > or
> > > select f,concat() as info from t2;
> > > returns equivalent
> > > 
> > > select f,('f4:' || f4 || ' - f5:' || f5 || ' - f6:' || f6) as x from t2;
> > > 
> -- 
> Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
>