Re: [GENERAL] dynamic field names in a function.
will trillich <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] dynamic field names in a function.
On Sat, Mar 31, 2001 at 12:42:29AM -0500, Tom Lane wrote: > Soma Interesting <[EMAIL PROTECTED]> 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. 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? [EMAIL PROTECTED] http://sourceforge.net/projects/newbiedoc -- we need your brain! http://www.dontUthink.com/ -- your brain needs us! ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] dynamic field names in a function.
Soma Interesting <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] dynamic field names in a function.
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 <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] dynamic field names in a function.
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? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [GENERAL] 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?
[EMAIL PROTECTED]
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
RE: [GENERAL] dynamic field names in a function.
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. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
RE: [GENERAL] dynamic field names in a function.
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:[EMAIL PROTECTED]] > Sent: Thursday, March 29, 2001 4:39 PM > To: [EMAIL PROTECTED] > 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 [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
