pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Morgan Curley <mcurley@e4media.com>
Cc: pgsql-general@postgresql.org
Cc: pgsql-sql@postgresql.org
Subject: Re: Inserts in triggers
Date: Wed, 25 Jul 2001 11:31:49 -0400
Message-ID: <21316.996075109@sss.pgh.pa.us> (raw)
In-Reply-To: <4.3.2.7.2.20010725030649.024f7de8@e4media.com>
References: <4.3.2.7.2.20010725030649.024f7de8@e4media.com>

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



view thread (4+ messages)

Message-ID: <21316.996075109@sss.pgh.pa.us>
Permalink:  ../21316.996075109@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/21316.996075109@sss.pgh.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: tgl@sss.pgh.pa.us, mcurley@e4media.com, pgsql-general@postgresql.org
  Subject: Re: Inserts in triggers
  In-Reply-To: <21316.996075109@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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