Date: Wed, 23 Jul 2003 11:12:01 -0700
From: elein <elein@varlena.com>
To: Robert Treat <xzilla@users.sourceforge.net>
Cc: elein@varlena.com, pgsql-sql@postgresql.org
Subject: Re: obtuse plpgsql function needs
Message-ID: <20030723111201.D20411@cookie>
References: <1058913112.22259.271.camel@camel> <20030722163335.A18881@cookie>
	<1058965609.22259.292.camel@camel>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <1058965609.22259.292.camel@camel>
User-Agent: Mutt/1.3.22.1i


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;
and
	select 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'm not sure.
		myconcat( t1 ); or possibly myconcat (t1.*);
	* Ability to know the number of columns in the RECORD
		A pg_catalog query
	* 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
>