pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Inserts in triggers
4+ messages / 3 participants
[nested] [flat]

* Inserts in triggers
@ 2001-07-25 07:11 Morgan Curley <mcurley@e4media.com>
  2001-07-25 08:56 ` Re: Inserts in triggers Richard Huxton <dev@archonet.com>
  2001-07-25 15:31 ` Re: Inserts in triggers Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 2 replies; 4+ messages in thread

From: Morgan Curley @ 2001-07-25 07:11 UTC (permalink / raw)
  To: pgsql-general@postgresql.org; pgsql-sql

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

^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Inserts in triggers
  2001-07-25 07:11 Inserts in triggers Morgan Curley <mcurley@e4media.com>
@ 2001-07-25 08:56 ` Richard Huxton <dev@archonet.com>
  1 sibling, 0 replies; 4+ messages in thread

From: Richard Huxton @ 2001-07-25 08:56 UTC (permalink / raw)
  To: pgsql-general@postgresql.org; pgsql-sql; Morgan Curley <mcurley@e4media.com>

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




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Inserts in triggers
  2001-07-25 07:11 Inserts in triggers Morgan Curley <mcurley@e4media.com>
@ 2001-07-25 15:31 ` Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 0 replies; 4+ messages in thread

From: Tom Lane @ 2001-07-25 15:31 UTC (permalink / raw)
  To: Morgan Curley <mcurley@e4media.com>; +Cc: pgsql-general@postgresql.org; pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Inserts in triggers
@ 2001-07-25 07:12 Morgan Curley <mcurley@e4media.com>
  0 siblings, 0 replies; 4+ messages in thread

From: Morgan Curley @ 2001-07-25 07:12 UTC (permalink / raw)
  To: pgsql-general@postgresql.org; pgsql-sql

Whoops,
using pg 7.1.2

^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2001-07-25 15:31 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-07-25 07:11 Inserts in triggers Morgan Curley <mcurley@e4media.com>
2001-07-25 08:56 ` Richard Huxton <dev@archonet.com>
2001-07-25 15:31 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-07-25 07:12 Inserts in triggers Morgan Curley <mcurley@e4media.com>

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