From mcurley@e4media.com Wed Jul 25 13:10:04 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 f6PDA4f16603; Wed, 25 Jul 2001 09:10:04 -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 IAA17695; Wed, 25 Jul 2001 08:08:20 -0500 Message-Id: <4.3.2.7.2.20010725085946.023e24a8@e4media.com> X-Sender: mcurley@e4media.com X-Mailer: QUALCOMM Windows Eudora Version 4.3.2 Date: Wed, 25 Jul 2001 09:09:54 -0400 To: , From: Morgan Curley Subject: Re: Inserts in triggers Follow Up In-Reply-To: <009301c114e7$dd2e8960$1001a8c0@archonet.com> References: <4.3.2.7.2.20010725030649.024f7de8@e4media.com> Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="=====================_173738622==_.ALT" X-Archive-Number: 200107/907 X-Sequence-Number: 13027 --=====================_173738622==_.ALT Content-Type: text/plain; charset="us-ascii"; format=flowed 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: >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 > > >---------------------------(end of broadcast)--------------------------- >TIP 5: Have you checked our extensive FAQ? > >http://www.postgresql.org/users-lounge/docs/faq.html --=====================_173738622==_.ALT Content-Type: text/html; charset="us-ascii" 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:
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
--=====================_173738622==_.ALT-- From josh@agliodbs.com Wed Jul 25 15:14:20 2001 Received: from davinci.ethosmedia.com (davinci.ethosmedia.com [209.10.40.250]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f6PFEKf36925 for ; Wed, 25 Jul 2001 11:14:20 -0400 (EDT) (envelope-from josh@agliodbs.com) Received: from [209.10.40.250] (account ) by davinci.ethosmedia.com (CommuniGate Pro WebUser 3.4.1) with HTTP id 91191; Wed, 25 Jul 2001 08:14:09 -0700 From: "Josh Berkus" Subject: Re: Re: Inserts in triggers Follow Up To: Morgan Curley , X-Mailer: CommuniGate Pro Web Mailer v.3.4.1 Date: Wed, 25 Jul 2001 08:14:09 -0700 Message-ID: In-Reply-To: <4.3.2.7.2.20010725085946.023e24a8@e4media.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="_===91191====davinci.ethosmedia.com===_" X-Archive-Number: 200107/340 X-Sequence-Number: 4153 This is a multi-part MIME message --_===91191====davinci.ethosmedia.com===_ Content-Type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: 8bit Morgan, > 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 That's because the SELECT INTO variable and SELECT INTO record syntax are confusingly different. It's for records: SELECT INTO record_var * FROM ... for simple variables: SELECT column INTO variable FROM ... I'm not clear on the origin of the inconsistency; my guess is that jan copied it over from PL/SQL. > 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. You should *always* do this. Most functions with identical column and variable names will confuse the compiler and result in errors. It'd have been nice if PL/pgSQL supported variable naming with a special character preifx (e.g. $variable). Does anyone know of a character that won't give the parser fits? Currently I'm using "v_", same as Richard. -Josh ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete information technology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco --_===91191====davinci.ethosmedia.com===_ Content-Type: text/plain Content-Disposition: attachment; filename="" Content-Transfer-Encoding: base64 DQo= --_===91191====davinci.ethosmedia.com===_ Content-Type: text/plain Content-Disposition: attachment; filename="" Content-Transfer-Encoding: base64 DQo= --_===91191====davinci.ethosmedia.com===_ Content-Type: text/plain Content-Disposition: attachment; filename="" Content-Transfer-Encoding: base64 DQo= --_===91191====davinci.ethosmedia.com===_-- From tgl@sss.pgh.pa.us Wed Jul 25 15:43:19 2001 Received: from sss.pgh.pa.us ([192.204.191.242]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f6PFhJf50390 for ; Wed, 25 Jul 2001 11:43:19 -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 f6PFh6C21393; Wed, 25 Jul 2001 11:43:06 -0400 (EDT) To: "Josh Berkus" cc: Morgan Curley , pgsql-sql@postgresql.org Subject: Re: Re: Inserts in triggers Follow Up In-reply-to: References: Comments: In-reply-to "Josh Berkus" message dated "Wed, 25 Jul 2001 08:14:09 -0700" Date: Wed, 25 Jul 2001 11:43:06 -0400 Message-ID: <21390.996075786@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200107/343 X-Sequence-Number: 4157 "Josh Berkus" writes: > That's because the SELECT INTO variable and SELECT INTO record syntax > are confusingly different. It's > for records: > SELECT INTO record_var * FROM ... > for simple variables: > SELECT column INTO variable FROM ... > I'm not clear on the origin of the inconsistency; my guess is that jan > copied it over from PL/SQL. No, this isn't right. If you check the source code you will discover that plpgsql is extremely lax about the positioning of the INTO clause, and will in fact accept it almost anywhere. Datatype has nothing to do with this. (It probably should be stricter, but at this point I doubt we could change it without drawing howls of anguish from those who did it the other way.) I'm not sure about Morgan's problem, but I suspect it isn't in the part of the query that he showed us. An unexpected substitution in the WHERE clause seems like a plausible theory. regards, tom lane From josh@agliodbs.com Wed Jul 25 15:53:57 2001 Received: from davinci.ethosmedia.com (davinci.ethosmedia.com [209.10.40.250]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f6PFrvf79405 for ; Wed, 25 Jul 2001 11:53:57 -0400 (EDT) (envelope-from josh@agliodbs.com) Received: from [209.10.40.250] (account ) by davinci.ethosmedia.com (CommuniGate Pro WebUser 3.4.1) with HTTP id 91242; Wed, 25 Jul 2001 08:53:45 -0700 From: "Josh Berkus" Subject: Re: Re: Inserts in triggers Follow Up To: Tom Lane Cc: pgsql-sql@postgresql.org X-Mailer: CommuniGate Pro Web Mailer v.3.4.1 Date: Wed, 25 Jul 2001 08:53:45 -0700 Message-ID: In-Reply-To: <21390.996075786@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="_===91242====davinci.ethosmedia.com===_" X-Archive-Number: 200107/343 X-Sequence-Number: 4156 This is a multi-part MIME message --_===91242====davinci.ethosmedia.com===_ Content-Type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: 8bit Tom, > No, this isn't right. If you check the source code you will discover > that plpgsql is extremely lax about the positioning of the INTO > clause, > and will in fact accept it almost anywhere. Datatype has nothing to > do with this. (It probably should be stricter, but at this point I > doubt we could change it without drawing howls of anguish from those > who did it the other way.) Thanks for setting me straight, Tom. I guess I combines some bugs in 7.0.2 with the original sketchy instructions and assumed that there were stricter syntactical rules than there are. This is good because it now means I can use the logical syntax of SELECT data INTO var ... which to me is easier to read, for everything. -Josh ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete information technology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco --_===91242====davinci.ethosmedia.com===_ Content-Type: text/plain Content-Disposition: attachment; filename="" Content-Transfer-Encoding: base64 DQo= --_===91242====davinci.ethosmedia.com===_ Content-Type: text/plain Content-Disposition: attachment; filename="" Content-Transfer-Encoding: base64 DQo= --_===91242====davinci.ethosmedia.com===_ Content-Type: text/plain Content-Disposition: attachment; filename="" Content-Transfer-Encoding: base64 DQo= --_===91242====davinci.ethosmedia.com===_-- From JanWieck@Yahoo.com Mon Jul 30 14:18:55 2001 Received: from smtp010.mail.yahoo.com (smtp010.mail.yahoo.com [216.136.173.30]) by postgresql.org (8.11.3/8.11.1) with SMTP id f6UEItf44735 for ; Mon, 30 Jul 2001 10:18:55 -0400 (EDT) (envelope-from janwieck@yahoo.com) Received: from jupiter.us.greatbridge.com (65.196.69.55) by smtp.mail.vip.sc5.yahoo.com with SMTP; 30 Jul 2001 14:18:51 -0000 X-Apparently-From: Received: (from wieck@localhost) by jupiter.us.greatbridge.com (8.11.2/8.11.2) id f6UEQpM06972; Mon, 30 Jul 2001 10:26:51 -0400 From: Jan Wieck Message-Id: <200107301426.f6UEQpM06972@jupiter.us.greatbridge.com> Subject: Re: Re: Inserts in triggers Follow Up In-Reply-To: <4.3.2.7.2.20010725085946.023e24a8@e4media.com> from Morgan Curley at "Jul 25, 2001 09:09:54 am" To: Morgan Curley Date: Mon, 30 Jul 2001 10:26:51 -0400 (EDT) CC: pgsql-general@postgresql.org, pgsql-sql@postgresql.org X-Mailer: ELM [version 2.4ME+ PL68 (25)] MIME-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-Archive-Number: 200107/1023 X-Sequence-Number: 13143 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