Thread: Inserts in triggers
Are there server setting that need to be set in order to do inserts from trigger functions?
I have been trying to load data into a table which would trigger an insert into another table with one or more of the NEW values.
I keep getting: ERROR: parser: parse error at or near "$1"
and the DEBUG log entry points to the line with the insert on it.
i.e.
INSERT events ( event_id, sport_id ,name ,feed_code, start_time, active_flag, create_timestamp, update_timestamp ) VALUES ( event_id ,sport_id ,feed_code ,feed_code, create_timestamp, 1, create_timestamp, create_timestamp );
where the values list is a list of predefined vars.
Does anyone see why this statement wouldn't work.
All required fields are present ant not null, all foreign keys exist.
thanks,
Morgan
I have been trying to load data into a table which would trigger an insert into another table with one or more of the NEW values.
I keep getting: ERROR: parser: parse error at or near "$1"
and the DEBUG log entry points to the line with the insert on it.
i.e.
INSERT events ( event_id, sport_id ,name ,feed_code, start_time, active_flag, create_timestamp, update_timestamp ) VALUES ( event_id ,sport_id ,feed_code ,feed_code, create_timestamp, 1, create_timestamp, create_timestamp );
where the values list is a list of predefined vars.
Does anyone see why this statement wouldn't work.
All required fields are present ant not null, all foreign keys exist.
thanks,
Morgan
From: "Morgan Curley" <mcurley@e4media.com> > Are there server setting that need to be set in order to do inserts from > trigger functions? Don't think so. Triggers run with the permissions of the creator, not the caller though so you might want to look into that. > I keep getting: ERROR: parser: parse error at or near "$1" > and the DEBUG log entry points to the line with the insert on it. > i.e. Could you quote a few lines of code either side - especially something with the $1 > INSERT events ( event_id, sport_id ,name ,feed_code, start_time, > active_flag, create_timestamp, update_timestamp ) VALUES ( event_id > ,sport_id ,feed_code ,feed_code, create_timestamp, 1, create_timestamp, > create_timestamp ); > > where the values list is a list of predefined vars. Well it should be INSERT INTO ... but I presume this is a cut & paste issue. So you have variable names the same as all the field names? I'd probably change them (stick v_ on the front or something). Even if plpgsql doesn't get confused readers will. - Richard Huxton
Well, I couldn't figure out why my INSERT INTO wasn't working so I went with using EXECUTE and concat'ing everything together.
One other problem I am having in that proc is SELECT'ing INTO a var.
declare
id lookup_sports.sport_id%TYPE;
begin
SELECT INTO id sport_id FROM lookup_sports WHERE....some clause
id is always null
but the following works
declare
rs RECORD;
id lookup_sports.sport_id%TYPE;
begin
SELECT INTO rs * FROM lookup_sports WHERE....some clause
id := rs.sport_id
this doesn't work either
SELECT INTO rs sport_id FROM lookup_sports WHERE....some clause
Any ideas
Thanks,
Morgan
P.S.
good suggestion to differentiate my vars from my columns. For the sake of expediency I named them the same so when I was writing the insert statements I could just copy/past my columns list into my values list.
Morgan
At 04:56 AM 7/25/2001, Richard Huxton wrote:
One other problem I am having in that proc is SELECT'ing INTO a var.
declare
id lookup_sports.sport_id%TYPE;
begin
SELECT INTO id sport_id FROM lookup_sports WHERE....some clause
id is always null
but the following works
declare
rs RECORD;
id lookup_sports.sport_id%TYPE;
begin
SELECT INTO rs * FROM lookup_sports WHERE....some clause
id := rs.sport_id
this doesn't work either
SELECT INTO rs sport_id FROM lookup_sports WHERE....some clause
Any ideas
Thanks,
Morgan
P.S.
good suggestion to differentiate my vars from my columns. For the sake of expediency I named them the same so when I was writing the insert statements I could just copy/past my columns list into my values list.
Morgan
At 04:56 AM 7/25/2001, Richard Huxton wrote:
From: "Morgan Curley" <mcurley@e4media.com>
> Are there server setting that need to be set in order to do inserts from
> trigger functions?
Don't think so. Triggers run with the permissions of the creator, not the
caller though so you might want to look into that.
> I keep getting: ERROR: parser: parse error at or near "$1"
> and the DEBUG log entry points to the line with the insert on it.
> i.e.
Could you quote a few lines of code either side - especially something with
the $1
> INSERT events ( event_id, sport_id ,name ,feed_code, start_time,
> active_flag, create_timestamp, update_timestamp ) VALUES ( event_id
> ,sport_id ,feed_code ,feed_code, create_timestamp, 1, create_timestamp,
> create_timestamp );
>
> where the values list is a list of predefined vars.
Well it should be INSERT INTO ... but I presume this is a cut & paste issue.
So you have variable names the same as all the field names? I'd probably
change them (stick v_ on the front or something). Even if plpgsql doesn't
get confused readers will.
- Richard Huxton
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Morgan Curley <mcurley@e4media.com> writes:
> I keep getting: ERROR: parser: parse error at or near "$1"
> and the DEBUG log entry points to the line with the insert on it.
> i.e.
> INSERT events ( event_id, sport_id ,name ,feed_code, start_time,
> active_flag, create_timestamp, update_timestamp ) VALUES ( event_id
> ,sport_id ,feed_code ,feed_code, create_timestamp, 1, create_timestamp,
> create_timestamp );
I think your problem is that this gets converted into something along
the line of
INSERT events ( $1, $2, ...) VALUES ( $1, $2, ...)
ie, the plpgsql parser is too stupid to know that it should substitute
values for only one set of occurrences of plpgsql variable names, and
not the other set. You could check this by running the function with
query logging turned on, and seeing what gets reported to the log.
Other than not using an INSERT target list, you could avoid this by
double-quoting the target list entries, or by not naming your plpgsql
variables the same as SQL columns you are referencing in your queries.
regards, tom lane
Morgan Curley wrote:
> Well, I couldn't figure out why my INSERT INTO wasn't working so I went
> with using EXECUTE and concat'ing everything together.
The problem was that your local variable names where
identical to column names used in the tables. That confuses
the PL/pgSQL query engine. Tom pointed that out already,
maybe not clear enough.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com