pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feed(trigger function) -> ERROR: NEW used in non-rule query
7+ messages / 6 participants
[nested] [flat]
* (trigger function) -> ERROR: NEW used in non-rule query
@ 2003-07-18 08:22 Terence Kearns <terencek@isd.canberra.edu.au>
0 siblings, 1 reply; 7+ messages in thread
From: Terence Kearns @ 2003-07-18 08:22 UTC (permalink / raw)
To: pgsql-sql
I'm writing a trigger which wants to reference the pre-defined NEW record.
I can do this
idval := NEW.blah;
This works fine!!!
What I really need to do is
idval := NEW.(quote_ident(TG_ARGV[3]));
or this
idval := NEW.(TG_ARGV[3]);
unfortunately this returns
"ERROR: NEW used in non-rule query"
I've also tried using
EXECUTE ''SELECT NEW.'' || quote_ident(TG_ARGV[3]);
(to test if I can use a FOR-IN-EXECUTE to extract it)
and it produces exactly the same error. Obviously NEW goes out of scope
for some reason. I can understand the case with EXECUTE but the other
statements should have worked :(
This is very frustrating because it is the *ONLY* thing standing in the
way of me solving a larger problem (which I will use to repsond to
another thread in this forum).
Does anyone know how to access an arbitarily specified field of the NEW
record in a trigger function. I need this because arguments to a
function in the trigger definition cannot be NEW.blah, they have to be
static/literals (which is likely to be for the same reason that NEW goes
out of scope any time I try to do something dynamic when evaluating a
field on it).
FYI: ref doco at
http://www.postgresql.org/docs/7.3/interactive/plpgsql-trigger.html
using pg 7.3.3
--
Terence Kearns ~ ph: +61 2 6201 5516
IT Database/Applications Developer
Enterprise Information Systems
Client Services Division
University of Canberra
www.canberra.edu.au
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: (trigger function) -> ERROR: NEW used in non-rule query
@ 2003-07-18 09:03 Richard Huxton <dev@archonet.com>
parent: Terence Kearns <terencek@isd.canberra.edu.au>
0 siblings, 1 reply; 7+ messages in thread
From: Richard Huxton @ 2003-07-18 09:03 UTC (permalink / raw)
To: Terence Kearns <terencek@isd.canberra.edu.au>; pgsql-sql
On Friday 18 Jul 2003 9:22 am, Terence Kearns wrote:
> I'm writing a trigger which wants to reference the pre-defined NEW record.
>
> I can do this
> idval := NEW.blah;
>
> This works fine!!!
>
> What I really need to do is
> idval := NEW.(quote_ident(TG_ARGV[3]));
> or this
> idval := NEW.(TG_ARGV[3]);
I'll give you the short answer - no you can't do this (in plpgsql). Which
isn't to say it'll never be possible, but not at the moment.
Can you afford to look at an alternative language? I'd suggest looking at TCL,
although I must admit I've never used it myself.
--
Richard Huxton
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: (trigger function) -> ERROR: NEW used in non-rule query
@ 2003-07-18 09:18 Terence Kearns <terencek@isd.canberra.edu.au>
parent: Richard Huxton <dev@archonet.com>
0 siblings, 2 replies; 7+ messages in thread
From: Terence Kearns @ 2003-07-18 09:18 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: Terence Kearns <terencek@isd.canberra.edu.au>; pgsql-sql
Richard Huxton wrote:
> On Friday 18 Jul 2003 9:22 am, Terence Kearns wrote:
>
>>I'm writing a trigger which wants to reference the pre-defined NEW record.
>>
>>I can do this
>>idval := NEW.blah;
>>
>>This works fine!!!
>>
>>What I really need to do is
>>idval := NEW.(quote_ident(TG_ARGV[3]));
>>or this
>>idval := NEW.(TG_ARGV[3]);
>
>
> I'll give you the short answer - no you can't do this (in plpgsql). Which
> isn't to say it'll never be possible, but not at the moment.
>
> Can you afford to look at an alternative language? I'd suggest looking at TCL,
> although I must admit I've never used it myself.
>
>
damn. There's realy no other way for me to access the value since the
trigger function will be called from different relations.
I tried idval := (''NEW.'' || TG_ARGV[3]); but recieved
ERROR: pf_atoi: error in "NEW.blah": can't parse "NEW.blah"
Oh well, can't win them all :/
Well I suppose I could try TCL. The problem is that there is little to
no documentation on postgres stored procedures in TCL and I've never
even seen the language before. None the less, I'll look into it. It's
almost worth it. If that fails, I may even try perl <shudders>. if that
files, I will try to get --with-python to configure (which it's refusing
to do at the moment even though I have a working installation).
--
Terence Kearns ~ ph: +61 2 6201 5516
IT Database/Applications Developer
Enterprise Information Systems
Client Services Division
University of Canberra
www.canberra.edu.au
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: (trigger function) -> ERROR: NEW used in non-rule query
@ 2003-07-18 13:06 Richard Huxton <richardh@archonet.com>
parent: Terence Kearns <terencek@isd.canberra.edu.au>
1 sibling, 0 replies; 7+ messages in thread
From: Richard Huxton @ 2003-07-18 13:06 UTC (permalink / raw)
To: Terence Kearns <terencek@isd.canberra.edu.au>; Richard Huxton <dev@archonet.com>; +Cc: Terence Kearns <terencek@isd.canberra.edu.au>; pgsql-sql
On Friday 18 July 2003 10:18, Terence Kearns wrote:
> damn. There's realy no other way for me to access the value since the
> trigger function will be called from different relations.
>
> I tried idval := (''NEW.'' || TG_ARGV[3]); but recieved
> ERROR: pf_atoi: error in "NEW.blah": can't parse "NEW.blah"
>
> Oh well, can't win them all :/
Well, not this one at the moment, anyway.
> Well I suppose I could try TCL. The problem is that there is little to
> no documentation on postgres stored procedures in TCL and I've never
> even seen the language before. None the less, I'll look into it. It's
> almost worth it. If that fails, I may even try perl <shudders>. if that
> files, I will try to get --with-python to configure (which it's refusing
> to do at the moment even though I have a working installation).
Not sure if you can write triggers in perl, and I think python is going
untrusted only (which means you need to be an admin to create functions).
- Richard Huxton
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: (trigger function) -> ERROR: NEW used in non-rule query
@ 2003-07-18 16:38 Josh Berkus <josh@agliodbs.com>
parent: Terence Kearns <terencek@isd.canberra.edu.au>
1 sibling, 2 replies; 7+ messages in thread
From: Josh Berkus @ 2003-07-18 16:38 UTC (permalink / raw)
To: Terence Kearns <terencek@isd.canberra.edu.au>; Richard Huxton <dev@archonet.com>; +Cc: Terence Kearns <terencek@isd.canberra.edu.au>; pgsql-sql
Terence,
> Oh well, can't win them all :/
Nope. I'll suggest that for the TODO list ... we already have several
requests for added features for PL/pgSQL. The problem is that we currently
don't have a lead developer for PL/pgSQL, so the language has rather
stagnated.
> Well I suppose I could try TCL. The problem is that there is little to
> no documentation on postgres stored procedures in TCL and I've never
> even seen the language before. None the less, I'll look into it. It's
> almost worth it. If that fails, I may even try perl <shudders>.
And what's wrong with Perl? Other than the inability to write triggers with
it? (We want to enable triggers in PL/perl, but that functionality isn't
coming until at least 7.5).
--
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: (trigger function) -> ERROR: NEW used in non-rule query
@ 2003-07-18 16:49 Dmitry Tkach <dmitry@openratings.com>
parent: Josh Berkus <josh@agliodbs.com>
1 sibling, 0 replies; 7+ messages in thread
From: Dmitry Tkach @ 2003-07-18 16:49 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: Terence Kearns <terencek@isd.canberra.edu.au>; Richard Huxton <dev@archonet.com>; pgsql-sql
Josh Berkus wrote:
>>Well I suppose I could try TCL. The problem is that there is little to
>>no documentation on postgres stored procedures in TCL and I've never
>>even seen the language before. None the less, I'll look into it. It's
>>almost worth it. If that fails, I may even try perl <shudders>.
>>
>>
>
>And what's wrong with Perl? Other than the inability to write triggers with
>it?
>
That's *exactly* "what's wrong" with it :-)
If I understand what he is talking about correctly, his whole problem is
that he is writing a trigger :-)
Dima
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: (trigger function) -> ERROR: NEW used in non-rule query
@ 2003-07-18 23:13 Terence Kearns <terencek@cts.canberra.edu.au>
parent: Josh Berkus <josh@agliodbs.com>
1 sibling, 0 replies; 7+ messages in thread
From: Terence Kearns @ 2003-07-18 23:13 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: Richard Huxton <dev@archonet.com>; pgsql-sql
Josh Berkus wrote:
> Terence,
>
>
>>Oh well, can't win them all :/
>
>
> Nope. I'll suggest that for the TODO list ... we already have several
> requests for added features for PL/pgSQL. The problem is that we currently
> don't have a lead developer for PL/pgSQL, so the language has rather
> stagnated.
>
Well beggers can't be choosers. I find it entirely amazing that one has
access to such a capable product entirely free of charge :)
While people like myself may raise issues from time to time and
sometimes express frustration, it doesn't mean that we don't think that
postgres (and associated tools) is absolutely awsome. I'm more than
content to "make do" ;^) with what postgres has now.
>
>>Well I suppose I could try TCL. The problem is that there is little to
>>no documentation on postgres stored procedures in TCL and I've never
>>even seen the language before. None the less, I'll look into it. It's
>>almost worth it. If that fails, I may even try perl <shudders>.
>
>
> And what's wrong with Perl? Other than the inability to write triggers with
> it? (We want to enable triggers in PL/perl, but that functionality isn't
> coming until at least 7.5).
>
Let's just say I don't belong to the very large community who LOVE the
language. Don't get too excited, it's just my personal preference. I
don't find the language "easy" at all (which is how it is towted).
One of these days when I get "spare time" (and the moon is blue), then I
will hit it again and maybe do it justice in terms of effort.
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2003-07-18 23:13 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-07-18 08:22 (trigger function) -> ERROR: NEW used in non-rule query Terence Kearns <terencek@isd.canberra.edu.au>
2003-07-18 09:03 ` Richard Huxton <dev@archonet.com>
2003-07-18 09:18 ` Terence Kearns <terencek@isd.canberra.edu.au>
2003-07-18 13:06 ` Richard Huxton <richardh@archonet.com>
2003-07-18 16:38 ` Josh Berkus <josh@agliodbs.com>
2003-07-18 16:49 ` Dmitry Tkach <dmitry@openratings.com>
2003-07-18 23:13 ` Terence Kearns <terencek@cts.canberra.edu.au>
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