From: elein <elein@varlena.com>
To: Robert Treat <xzilla@users.sourceforge.net>
Cc: elein@varlena.com
Cc: pgsql-sql@postgresql.org
Subject: Re: obtuse plpgsql function needs
Date: Wed, 23 Jul 2003 11:12:01 -0700
Message-ID: <20030723111201.D20411@cookie> (raw)
In-Reply-To: <1058965609.22259.292.camel@camel>
References: <1058913112.22259.271.camel@camel>
<20030722163335.A18881@cookie>
<1058965609.22259.292.camel@camel>
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
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: elein@varlena.com, xzilla@users.sourceforge.net
Subject: Re: obtuse plpgsql function needs
In-Reply-To: <20030723111201.D20411@cookie>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox