Re: dynamic field names in a function.

On Fri, Mar 30, 2001 at 10:57:42AM -0800, Soma Interesting wrote:
> In the following, is there something I can do so that postgres will
> evaluate NEW.qty to NEW.name, treating qty as a variable and evaluating it
> before evaluating the field reference? At this time it errors on an INSERT
> with: "record new has no field qty".
>
> CREATE FUNCTION func_test() RETURNS opaque AS '
> DECLARE
> qty varchar(5);
> BEGIN
>
> qty := ''name'';
> NEW.qty := ''target'';
> return new;
> END;
> ' LANGUAGE 'plpgsql';

i was hoping for some such beast, too. apparently in 7.1 (doea
that even exist yet?) you can have one plpgsql procedure create a
string that happens to be plpgsql code that you can EXECUTE, and
as such you can have dynamically-created functions that'll do
what you want...

but from what i can tell, the answer to

-- can you do this?
select mytbl.[myvariable] ;

seems to be NO, since whatever is after the dot is taken as a
field name. (anybody who knows different is welcome to shoot me
down.)

if they had alternate syntax, such as

table{"fieldexpression"}
table->"fieldexpr"
table("fieldexpr")

maybe it'd be simpler to incorporate in a future incarnation of
the parser...?

come to think of it, field names can get quoted to hinder
otherwise dangrous parsing:

create table "this relation" ( "my field" as text ) ;
select "this relation"."my field" ;

why not allow variable-substitution in those instances? (grumble,
grumble...)

--

HOWEVER -- we do have arrays, don't forget... sometimes they can
be bent to do more than intended (but usually not!)

create table mailing(
person_id serial,
sent int4[],
prefs varchar[],
current int2
);
-- insert, update, munge and frob, then
select person_id,sent[current] from something;

--

oh, and if your PostgreSQL instance is new enough, you might have
PERL built in, which could make all of this moot. (now we just
need someone to DOCUMENT the sucker so we know how perl can talk
back to postgres for cross-lookups and such...)

--
does a brain cell think?

will(at)serensoft(dot)com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!