Thread: dynamic field names in a function.
I want to be able to reference NEW.field_0 though NEW.field_x where x is
coming from NEW.qty in a FOR loop of pl/pgsql function. Is this possible?
In other words:
FOR j IN 0..NEW.str LOOP
ans := ''q'' || i || ''a'' || j;
cor := ''q'' || i || ''c'' || j;
eval := 'q'' || i || ''e'' || j;
IF NEW.ans = NEW.cor
THEN NEW.eval := 1;
END IF;
END LOOP;
In my experience, the best way to find out answers like this is to try it out and see. Usually I find that I need to fiddle around with the syntax a bit (I believe it's called debugging) before getting something to work. Postgresql is very powerful; the capability to utilize that power comes at a price. In other words, be prepared to put in a solid investment if you want to see a return. (I'm not accustomed to preaching, but sometimes this just needs to be said). > -----Original Message----- > From: Soma Interesting [SMTP:dfunct@telus.net] > Sent: Thursday, March 29, 2001 4:39 PM > To: pgsql-general@postgresql.org > Subject: [GENERAL] dynamic field names in a function. > > > I want to be able to reference NEW.field_0 though NEW.field_x where x is > coming from NEW.qty in a FOR loop of pl/pgsql function. Is this possible? > > In other words: > > FOR j IN 0..NEW.str LOOP > > ans := ''q'' || i || ''a'' || j; > cor := ''q'' || i || ''c'' || j; > eval := 'q'' || i || ''e'' || j; > > IF NEW.ans = NEW.cor > THEN NEW.eval := 1; > END IF; > > END LOOP; > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
At 11:30 AM 3/30/2001 -0600, you wrote: >In my experience, the best way to find out answers like this is to try it >out and see. Usually I find that I need to fiddle around with the syntax a >bit (I believe it's called debugging) before getting something to work. >Postgresql is very powerful; the capability to utilize that power comes at a >price. In other words, be prepared to put in a solid investment if you want >to see a return. >(I'm not accustomed to preaching, but sometimes this just needs to be said). Well its good you don't do it often because your not very good at it. ;) I've spent a reasonable about of time trying different approaches before posting my question here. If I had the confidence that what I was trying to do was certainly possible with pl/pgsql - then I'd pursue it until I made it work. I probably have more tenacity than you realize, despite it being so very, very obvious by my trying to get an answer in this mailing list. However, since I could declare a variable called id and have a column in the table called id and perhaps I want to reference NEW.id where id is the variable value not the column - then it would seem that whatever parser is at work may have some ambiguities to cope with. Thus I begin to doubt if its something that should be expected of pl/pgsql. I've not come across any way to make a variable reference more explicit to the parser in the postgres docs.... so I have no choice but to ask here. Then again it would make good sense if the parser did evaluated variables before evaluating field references... but the fact is, "I DON'T KNOW". Thanks.
Please.
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';
CREATE TRIGGER func_test_trigger BEFORE INSERT
ON test FOR EACH ROW
EXECUTE PROCEDURE func_test ();
At 03:43 PM 3/30/2001 -0600, you wrote: >I think I am beginning to understand your question. You want to know >whether a field name can be referenced using a variable as all or part of >the field name? I don't know for sure, but I can't make it work, and >apparently you can't either. >Your earlier message is still out on the wire, so someone more knowledgeable >may still answer. >If you expect the maximum number of question/answer pairs to be manageably >small, you could do a series of actions for each field by name, incrementing >a counter for each pair, and comparing the counter value to 'qty'. >Horrible, I know, but the best I can suggest right now. I'm going to quote your rephrasing of my question to the list, maybe your words will carry more weight as "valid" question. Another option would be to use Tcl or Pearl, would they be able to use variables as all or part of the field name?
An alternative would be to do the the work at the application level, i.e. the point where the data is collected. You could use any language you like for that. > -----Original Message----- > From: Soma Interesting [SMTP:dfunct@telus.net] > Sent: Friday, March 30, 2001 3:59 PM > To: Jeff Eckermann; pgsql-general@postgresql.org > Subject: RE: [GENERAL] dynamic field names in a function. > > At 03:43 PM 3/30/2001 -0600, you wrote: > >I think I am beginning to understand your question. You want to know > >whether a field name can be referenced using a variable as all or part of > >the field name? I don't know for sure, but I can't make it work, and > >apparently you can't either. > >Your earlier message is still out on the wire, so someone more > knowledgeable > >may still answer. > >If you expect the maximum number of question/answer pairs to be > manageably > >small, you could do a series of actions for each field by name, > incrementing > >a counter for each pair, and comparing the counter value to 'qty'. > >Horrible, I know, but the best I can suggest right now. > > I'm going to quote your rephrasing of my question to the list, maybe your > words will carry more weight as "valid" question. > > Another option would be to use Tcl or Pearl, would they be able to use > variables as all or part of the field name?
At 04:09 PM 3/30/2001 -0600, you wrote: >An alternative would be to do the the work at the application level, i.e. >the point where the data is collected. You could use any language you like >for that. This has ran through my mind as an option - the project is a web application with php and postgres, but a number of factors involved makes it really undesirable to do this at the application level - to the point that its worth my while to learn pl/pgsql, perl, or tcl to avoid it - or possibly even C - none of which I know... yet :) So I repeat the question, not to be obnoxious - but to be sure, "does anyone *know* if any of the languages available for writing functions would allow a field name to be referenced using a variable as all or part of the field name?
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@serensoft.com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!
At 05:47 PM 3/30/2001 -0600, you wrote: >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...) Will, you are a powerful postgres wizard and never let anyone tell you otherwise. I may be able to use array's if postgres will allow using variables to reference points in the array. As for your comment about perl being built in, how can I find this out? I guess just try CREATE LANGUAGE specifying perl?
On Fri, Mar 30, 2001 at 04:39:21PM -0800, Soma Interesting wrote:
> At 05:47 PM 3/30/2001 -0600, you wrote:
> >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...)
>
> Will, you are a powerful postgres wizard and never let anyone tell you
> otherwise.
nice to know i can still fool some of the people some of the
time. :) let's just say i'm a half-hour ahead of you on at least
one topic... (probably at MOST, one topic.)
> I may be able to use array's if postgres will allow using variables to
> reference points in the array.
>
> As for your comment about perl being built in, how can I find this out? I
> guess just try CREATE LANGUAGE specifying perl?
oh, hell no. if it were that easy, my tone of voice would be much
more pleasing.
i'm using debian 2.2 (potato) where all we have to do is "apt-get
install" and the package is downloaded, installed and configured.
i've got 7.0.3potato which is a debian2.2 friendly incarnation --
i specifically installed it from samfundet.no/~tfheen because
on the debian user list, 'they' said it had perl built in.
alas... i do have fancy capabilities in 7.0.3 that weren't in
6.5.3, but still no plperl... bitchandmoan...
--
apparently what you're looking for is "plperl.so".
% locate plperl
/usr/share/doc/postgresql-doc/src/pl/plperl
/usr/share/doc/postgresql-doc/src/pl/plperl/README
that document shows me how, if i HAD plperl, i could get it
active within postgresql...
maybe you should search the postgresql site for keyword "plperl"
and see where it takes you...
--
does a brain cell think?
will@serensoft.com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!
At 06:47 PM 3/30/2001 -0600, you wrote: > > I may be able to use array's if postgres will allow using variables to > > reference points in the array. OK, so how do I reference a specific element of an array in a record within pl/pgsql. I've tried: NEW.name[1] NEW.name.1
On Thu, Mar 29, 2001 at 02:38:31PM -0800, Soma Interesting wrote: > > I want to be able to reference NEW.field_0 though NEW.field_x where x is > coming from NEW.qty in a FOR loop of pl/pgsql function. Is this possible? > > In other words: > > FOR j IN 0..NEW.str LOOP > > ans := ''q'' || i || ''a'' || j; > cor := ''q'' || i || ''c'' || j; > eval := 'q'' || i || ''e'' || j; > > IF NEW.ans = NEW.cor > THEN NEW.eval := 1; > END IF; > > END LOOP; I think maybe querying system catalogs might help your approach. I'm not entirely clear on what you're trying to do, but you can get the name of the relation that caused the trigger to fire (TG_RELNAME). Then query the pg_class table for the "oid" of the class where relnam = TG_RELNAME, join with pg_attribute on pg_class.oid = pg_attribute.attrelid and pg_attribute.attnum > 0 (to skip internal system fields). Then you have a set of records containing all of the field names for the relation which you can compare to the concatenation of your "field" and NEW.qty. Hope this is making some sense. Here's a quick example query on a known relation called "units". select pg_attribute.* from pg_attribute, pg_class where pg_attribute.attrelid = pg_class.oid and pg_class.relname = 'units' and pg_attribute.attnum > 0; You'll probably be most interested in "pg_attribute.attname". -- Eric G. Miller <egm2@jps.net>
Soma Interesting <dfunct@telus.net> writes:
> 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?
Plain PLSQL will not do this --- it wants to know field names when the
query is first compiled. (This is a feature, not a bug, since it
implies that the query can be optimized during compilation.)
You could use PLTCL or PLPERL instead. Both of them treat queries
as plain strings that you assemble out of whatever parts you please
and then pass to the query engine. Of course you pay the cost of
re-planning the query from scratch every time --- there's no free lunch.
In 7.1, PLSQL can do that trick too, via its new EXECUTE statement.
regards, tom lane
On Sat, Mar 31, 2001 at 12:42:29AM -0500, Tom Lane wrote: > Soma Interesting <dfunct@telus.net> writes: > > 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? > > Plain PLSQL will not do this --- it wants to know field names when the > query is first compiled. (This is a feature, not a bug, since it > implies that the query can be optimized during compilation.) > > You could use PLTCL or PLPERL instead. Both of them treat queries > as plain strings that you assemble out of whatever parts you please > and then pass to the query engine. Of course you pay the cost of > re-planning the query from scratch every time --- there's no free lunch. <ding ding ding> there's that magic word PLPERL again... ! HOW do you find out if your postgresql (mine's 7.0.3) can support the plperl.so feature? And HOW do you go about installing it, if it does? What parts are necessary? -- does a brain cell think? will@serensoft.com http://sourceforge.net/projects/newbiedoc -- we need your brain! http://www.dontUthink.com/ -- your brain needs us!
On Fri, Mar 30, 2001 at 06:02:27PM -0800, Soma Interesting wrote:
> At 06:47 PM 3/30/2001 -0600, you wrote:
> > > I may be able to use array's if postgres will allow using variables to
> > > reference points in the array.
>
>
> OK, so how do I reference a specific element of an array in a record within
> pl/pgsql.
>
> I've tried:
>
> NEW.name[1]
> NEW.name.1
hmm. maybe it's NEW that's the problem...?
create table gunk(
val varchar[]
);
insert into gunk
values (
'{"first string","another value","the end"}'
) ;
select val[2] from gunk;
val
---------------
another value
(1 row)
i'd think
table.field[index]
should do the trick, even in plpgsql:
myvar := select name[index] from new;
myvar := new.name[somevariable];
myary := new.name;
myvar := myary[someindexvar];
no? (unless <guess> maybe the plpgsql compiler doesn't like
variable subscript values </guess>, which would warrant keel
hauling everyone responsible...)
--
does a brain cell think?
will@serensoft.com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!
will trillich <will@serensoft.com> writes: > HOW do you find out if your postgresql (mine's 7.0.3) can support > the plperl.so feature? And HOW do you go about installing it, if > it does? What parts are necessary? See http://www.postgresql.org/devel-corner/docs/postgres/plperl.html These are 7.1 docs but the info applies to 7.0 as well. The main trick is that you need a Perl installation that has a shared library for perl (libperl.so, not libperl.a). This usually requires building Perl from source, since it's not the default configuration. It's easy enough if you build Perl from source, just say "yes" when Perl's interactive configure script asks if you want a shlib. regards, tom lane
On Sat, Mar 31, 2001 at 07:57:59PM -0500, Tom Lane wrote: > will trillich <will@serensoft.com> writes: > > HOW do you find out if your postgresql (mine's 7.0.3) can support > > the plperl.so feature? And HOW do you go about installing it, if > > it does? What parts are necessary? > > See > http://www.postgresql.org/devel-corner/docs/postgres/plperl.html > These are 7.1 docs but the info applies to 7.0 as well. > > The main trick is that you need a Perl installation that has a shared > library for perl (libperl.so, not libperl.a). This usually requires > building Perl from source, since it's not the default configuration. > It's easy enough if you build Perl from source, just say "yes" when > Perl's interactive configure script asks if you want a shlib. i thought it was looking for plperl.so... ? thanks! -- does a brain cell think? will@serensoft.com http://sourceforge.net/projects/newbiedoc -- we need your brain! http://www.dontUthink.com/ -- your brain needs us!
will trillich <will@serensoft.com> writes:
>> The main trick is that you need a Perl installation that has a shared
>> library for perl (libperl.so, not libperl.a). This usually requires
>> building Perl from source, since it's not the default configuration.
>> It's easy enough if you build Perl from source, just say "yes" when
>> Perl's interactive configure script asks if you want a shlib.
> i thought it was looking for plperl.so... ?
But plperl.so depends on libperl.so.
regards, tom lane
At 12:42 AM 3/31/2001 -0500, you wrote: >Plain PLSQL will not do this --- it wants to know field names when the >query is first compiled. (This is a feature, not a bug, since it >implies that the query can be optimized during compilation.) > >You could use PLTCL or PLPERL instead. Both of them treat queries >as plain strings that you assemble out of whatever parts you please >and then pass to the query engine. Of course you pay the cost of >re-planning the query from scratch every time --- there's no free lunch. > >In 7.1, PLSQL can do that trick too, via its new EXECUTE statement. You neglected to mention that triggers can't call perl functions. Had you mentioned this - I'd not have bothered spending several days trying to get perl working with postgres. YOU SHOULD EXPLICITLY SAY THIS IN YOUR MANUAL!!!