Re: Inserts in triggers

Morgan Curley <mcurley(at)e4media(dot)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