From mcurley@e4media.com Wed Jul 25 08:16:42 2001 Received: from victoria.host4u.net (victoria.host4u.net [216.71.64.77]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f6P8Ggf89478; Wed, 25 Jul 2001 04:16:42 -0400 (EDT) (envelope-from mcurley@e4media.com) Received: from morganc.e4media.com (adsl-162-83-164-253.nyc.adsl.bellatlantic.net [162.83.164.253]) by victoria.host4u.net (8.8.5/8.8.5) with ESMTP id CAA19962; Wed, 25 Jul 2001 02:10:14 -0500 Message-Id: <4.3.2.7.2.20010725030649.024f7de8@e4media.com> X-Sender: mcurley@e4media.com X-Mailer: QUALCOMM Windows Eudora Version 4.3.2 Date: Wed, 25 Jul 2001 03:11:48 -0400 To: , From: Morgan Curley Subject: Inserts in triggers In-Reply-To: Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="=====================_152249002==_.ALT" X-Archive-Number: 200107/898 X-Sequence-Number: 13019 --=====================_152249002==_.ALT Content-Type: text/plain; charset="us-ascii"; format=flowed 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 --=====================_152249002==_.ALT Content-Type: text/html; charset="us-ascii" 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
--=====================_152249002==_.ALT-- From mcurley@e4media.com Wed Jul 25 08:16:41 2001 Received: from victoria.host4u.net (victoria.host4u.net [216.71.64.77]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f6P8Gff89472; Wed, 25 Jul 2001 04:16:41 -0400 (EDT) (envelope-from mcurley@e4media.com) Received: from morganc.e4media.com (adsl-162-83-164-253.nyc.adsl.bellatlantic.net [162.83.164.253]) by victoria.host4u.net (8.8.5/8.8.5) with ESMTP id CAA20005; Wed, 25 Jul 2001 02:10:46 -0500 Message-Id: <4.3.2.7.2.20010725031203.00b96b98@e4media.com> X-Sender: mcurley@e4media.com X-Mailer: QUALCOMM Windows Eudora Version 4.3.2 Date: Wed, 25 Jul 2001 03:12:20 -0400 To: , From: Morgan Curley Subject: Inserts in triggers Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="=====================_152281479==_.ALT" X-Archive-Number: 200107/899 X-Sequence-Number: 13019 --=====================_152281479==_.ALT Content-Type: text/plain; charset="us-ascii"; format=flowed Whoops, using pg 7.1.2 --=====================_152281479==_.ALT Content-Type: text/html; charset="us-ascii" Whoops,
using pg 7.1.2

--=====================_152281479==_.ALT-- From dev@archonet.com Wed Jul 25 09:03:48 2001 Received: from anchor-post-32.mail.demon.net (anchor-post-32.mail.demon.net [194.217.242.90]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f6P93lf13272; Wed, 25 Jul 2001 05:03:48 -0400 (EDT) (envelope-from dev@archonet.com) Received: from mwynhau.demon.co.uk ([193.237.186.96] helo=mainbox.archonet.com) by anchor-post-32.mail.demon.net with esmtp (Exim 2.12 #1) id 15PKZt-000P7D-0W; Wed, 25 Jul 2001 10:03:37 +0100 Received: from client (client.archonet.com [192.168.1.16]) by mainbox.archonet.com (Postfix) with SMTP id 5896315067; Wed, 25 Jul 2001 09:57:44 +0100 (BST) Message-ID: <009301c114e7$dd2e8960$1001a8c0@archonet.com> From: "Richard Huxton" To: , , "Morgan Curley" References: <4.3.2.7.2.20010725030649.024f7de8@e4media.com> Subject: Re: Inserts in triggers Date: Wed, 25 Jul 2001 09:56:35 +0100 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4133.2400 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400 X-Archive-Number: 200107/900 X-Sequence-Number: 13020 From: "Morgan Curley" > 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 From tgl@sss.pgh.pa.us Wed Jul 25 15:32:07 2001 Received: from sss.pgh.pa.us ([192.204.191.242]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f6PFW7f44968; Wed, 25 Jul 2001 11:32:07 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id f6PFVoC21319; Wed, 25 Jul 2001 11:31:50 -0400 (EDT) To: Morgan Curley cc: pgsql-general@postgresql.org, pgsql-sql@postgresql.org Subject: Re: Inserts in triggers In-reply-to: <4.3.2.7.2.20010725030649.024f7de8@e4media.com> References: <4.3.2.7.2.20010725030649.024f7de8@e4media.com> Comments: In-reply-to Morgan Curley message dated "Wed, 25 Jul 2001 03:11:48 -0400" Date: Wed, 25 Jul 2001 11:31:49 -0400 Message-ID: <21316.996075109@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200107/910 X-Sequence-Number: 13030 Morgan Curley 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