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

* Re:  Inserts in triggers Follow Up
@ 2001-07-25 13:09 Morgan Curley <mcurley@e4media.com>
  2001-07-25 15:14 ` Re: Re:  Inserts in triggers Follow Up Josh Berkus <josh@agliodbs.com>
  2001-07-30 14:26 ` Re: Re:  Inserts in triggers Follow Up Jan Wieck <JanWieck@Yahoo.com>
  0 siblings, 2 replies; 5+ messages in thread

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

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

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

* Re: Re:  Inserts in triggers Follow Up
  2001-07-25 13:09 Re:  Inserts in triggers Follow Up Morgan Curley <mcurley@e4media.com>
@ 2001-07-25 15:14 ` Josh Berkus <josh@agliodbs.com>
  2001-07-25 15:43   ` Re: Re: Inserts in triggers Follow Up Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 1 reply; 5+ messages in thread

From: Josh Berkus @ 2001-07-25 15:14 UTC (permalink / raw)
  To: Morgan Curley <mcurley@e4media.com>; pgsql-sql

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







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

* Re: Re: Inserts in triggers Follow Up
  2001-07-25 13:09 Re:  Inserts in triggers Follow Up Morgan Curley <mcurley@e4media.com>
  2001-07-25 15:14 ` Re: Re:  Inserts in triggers Follow Up Josh Berkus <josh@agliodbs.com>
@ 2001-07-25 15:43   ` Tom Lane <tgl@sss.pgh.pa.us>
  2001-07-25 15:53     ` Re: Re: Inserts in triggers Follow Up Josh Berkus <josh@agliodbs.com>
  0 siblings, 1 reply; 5+ messages in thread

From: Tom Lane @ 2001-07-25 15:43 UTC (permalink / raw)
  To: Josh Berkus <josh@agliodbs.com>; +Cc: Morgan Curley <mcurley@e4media.com>; pgsql-sql

"Josh Berkus" <josh@agliodbs.com> 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



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

* Re: Re: Inserts in triggers Follow Up
  2001-07-25 13:09 Re:  Inserts in triggers Follow Up Morgan Curley <mcurley@e4media.com>
  2001-07-25 15:14 ` Re: Re:  Inserts in triggers Follow Up Josh Berkus <josh@agliodbs.com>
  2001-07-25 15:43   ` Re: Re: Inserts in triggers Follow Up Tom Lane <tgl@sss.pgh.pa.us>
@ 2001-07-25 15:53     ` Josh Berkus <josh@agliodbs.com>
  0 siblings, 0 replies; 5+ messages in thread

From: Josh Berkus @ 2001-07-25 15:53 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql

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







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

* Re: Re:  Inserts in triggers Follow Up
  2001-07-25 13:09 Re:  Inserts in triggers Follow Up Morgan Curley <mcurley@e4media.com>
@ 2001-07-30 14:26 ` Jan Wieck <JanWieck@Yahoo.com>
  1 sibling, 0 replies; 5+ messages in thread

From: Jan Wieck @ 2001-07-30 14:26 UTC (permalink / raw)
  To: Morgan Curley <mcurley@e4media.com>; +Cc: pgsql-general@postgresql.org; pgsql-sql

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




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


end of thread, other threads:[~2001-07-30 14:26 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-07-25 13:09 Re:  Inserts in triggers Follow Up Morgan Curley <mcurley@e4media.com>
2001-07-25 15:14 ` Josh Berkus <josh@agliodbs.com>
2001-07-25 15:43   ` Tom Lane <tgl@sss.pgh.pa.us>
2001-07-25 15:53     ` Josh Berkus <josh@agliodbs.com>
2001-07-30 14:26 ` Jan Wieck <JanWieck@Yahoo.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