Re: 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:dfunct(at)telus(dot)net]
> Sent: Thursday, March 29, 2001 4:39 PM
> To: pgsql-general(at)postgresql(dot)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(at)postgresql(dot)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 ();

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!

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(at)serensoft(dot)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

Soma Interesting <dfunct(at)telus(dot)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(at)telus(dot)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(at)serensoft(dot)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(at)serensoft(dot)com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!

will trillich <will(at)serensoft(dot)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(at)serensoft(dot)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(at)serensoft(dot)com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!

will trillich <will(at)serensoft(dot)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!!!