pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedplpgsql strangeness with select into <variable>
5+ messages / 5 participants
[nested] [flat]
* plpgsql strangeness with select into <variable>
@ 2003-07-18 15:24 Reinoud van Leeuwen <reinoud@xs4all.nl>
0 siblings, 3 replies; 5+ messages in thread
From: Reinoud van Leeuwen @ 2003-07-18 15:24 UTC (permalink / raw)
To: pgsql-hackers@postgresql.org
I'm debugging a trigger in plpgsql and for some reason or the "select
into <var>" does not seem to work. Here is an unaltered snippet of my
trigger code:
raise notice ''this id : %'',NEW.id;
select into i_hierarchy_id
hierarchy_id
from link_def LD,
link L,
object_link OL
where OL.id = NEW.id
and L.id = OL.link_id
and LD.id = L.link_def_id;
raise notice ''i_hierarchy_id: %'',i_hierarchy_id;
in the log this results in:
NOTICE: this id : 5265
NOTICE: i_hierarchy_id: <NULL>
but when I perform the query on the command line I do get a result:
select hierarchy_id
from link_def LD,
link L,
object_link OL
where OL.id = 5264
and L.id = OL.link_id
and LD.id = L.link_def_id;
hierarchy_id
--------------
1
(1 row)
i_hierarchy_id is declared as integer and is not used before this code nor
as a column name anywhere.
Does anyone have a clue what is going wrong? I use Postgresql 7.3.3 on
FreeBSD 4.5.
--
__________________________________________________
"Nothing is as subjective as reality"
Reinoud van Leeuwen reinoud.v@n.leeuwen.net
http://www.xs4all.nl/~reinoud
__________________________________________________
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [HACKERS] plpgsql strangeness with select into <variable>
@ 2003-07-18 16:14 Josh Berkus <josh@agliodbs.com>
parent: Reinoud van Leeuwen <reinoud@xs4all.nl>
2 siblings, 1 reply; 5+ messages in thread
From: Josh Berkus @ 2003-07-18 16:14 UTC (permalink / raw)
To: Reinoud van Leeuwen <reinoud@xs4all.nl>; pgsql-sql
Reinoud,
First, I'm moving your question to PGSQL-SQL, which is the appropriate list,
not HACKERS. See my response at the bottom of your quote.
> I'm debugging a trigger in plpgsql and for some reason or the "select
> into <var>" does not seem to work. Here is an unaltered snippet of my
> trigger code:
>
> raise notice ''this id : %'',NEW.id;
>
> select into i_hierarchy_id
> hierarchy_id
> from link_def LD,
> link L,
> object_link OL
> where OL.id = NEW.id
> and L.id = OL.link_id
> and LD.id = L.link_def_id;
>
> raise notice ''i_hierarchy_id: %'',i_hierarchy_id;
>
>
> in the log this results in:
>
> NOTICE: this id : 5265
> NOTICE: i_hierarchy_id: <NULL>
>
> but when I perform the query on the command line I do get a result:
>
> select hierarchy_id
> from link_def LD,
> link L,
> object_link OL
> where OL.id = 5264
> and L.id = OL.link_id
> and LD.id = L.link_def_id;
>
> hierarchy_id
> --------------
> 1
> (1 row)
>
> i_hierarchy_id is declared as integer and is not used before this code nor
> as a column name anywhere.
>
> Does anyone have a clue what is going wrong? I use Postgresql 7.3.3 on
> FreeBSD 4.5.
Without seeing your full trigger code, I can't tell for sure. However, I
would guess that your "SELECT INTO" statement is querying data that has not
yet been created; it's an FK record waiting on a deferred trigger, or you're
using a BEFORE trigger and querying the record which has not yet been
committed.
--
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [HACKERS] plpgsql strangeness with select into <variable>
@ 2003-07-18 16:19 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 5+ messages in thread
From: Stephan Szabo @ 2003-07-18 16:19 UTC (permalink / raw)
To: Reinoud van Leeuwen <reinoud@xs4all.nl>; +Cc: Josh Berkus <josh@agliodbs.com>; pgsql-sql
On Fri, 18 Jul 2003, Josh Berkus wrote:
> > I'm debugging a trigger in plpgsql and for some reason or the "select
> > into <var>" does not seem to work. Here is an unaltered snippet of my
> > trigger code:
> >
> > raise notice ''this id : %'',NEW.id;
> >
> > select into i_hierarchy_id
> > hierarchy_id
> > from link_def LD,
> > link L,
> > object_link OL
> > where OL.id = NEW.id
> > and L.id = OL.link_id
> > and LD.id = L.link_def_id;
> >
> > raise notice ''i_hierarchy_id: %'',i_hierarchy_id;
> >
> >
> > in the log this results in:
> >
> > NOTICE: this id : 5265
> > NOTICE: i_hierarchy_id: <NULL>
> >
> > but when I perform the query on the command line I do get a result:
> >
> > select hierarchy_id
> > from link_def LD,
> > link L,
> > object_link OL
> > where OL.id = 5264
> > and L.id = OL.link_id
> > and LD.id = L.link_def_id;
> >
> > hierarchy_id
> > --------------
> > 1
> > (1 row)
> >
> > i_hierarchy_id is declared as integer and is not used before this code nor
> > as a column name anywhere.
> >
> > Does anyone have a clue what is going wrong? I use Postgresql 7.3.3 on
> > FreeBSD 4.5.
>
> Without seeing your full trigger code, I can't tell for sure. However, I
> would guess that your "SELECT INTO" statement is querying data that has not
> yet been created; it's an FK record waiting on a deferred trigger, or you're
> using a BEFORE trigger and querying the record which has not yet been
> committed.
Or perhaps you have a variable named hierarchy_id in the function which is
getting used rather than the column of the table in question. Seeing the
entire function might help.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: plpgsql strangeness with select into <variable>
@ 2003-07-18 17:55 Robert Treat <xzilla@users.sourceforge.net>
parent: Reinoud van Leeuwen <reinoud@xs4all.nl>
2 siblings, 0 replies; 5+ messages in thread
From: Robert Treat @ 2003-07-18 17:55 UTC (permalink / raw)
To: Reinoud van Leeuwen <reinoud@xs4all.nl>; +Cc: pgsql-hackers@postgresql.org
On Fri, 2003-07-18 at 11:24, Reinoud van Leeuwen wrote:
> I'm debugging a trigger in plpgsql and for some reason or the "select
> into <var>" does not seem to work. Here is an unaltered snippet of my
> trigger code:
>
> raise notice ''this id : %'',NEW.id;
>
> select into i_hierarchy_id
> hierarchy_id
> from link_def LD,
> link L,
> object_link OL
> where OL.id = NEW.id
> and L.id = OL.link_id
> and LD.id = L.link_def_id;
>
> raise notice ''i_hierarchy_id: %'',i_hierarchy_id;
>
>
> in the log this results in:
>
> NOTICE: this id : 5265
> NOTICE: i_hierarchy_id: <NULL>
>
> but when I perform the query on the command line I do get a result:
>
> select hierarchy_id
> from link_def LD,
> link L,
> object_link OL
> where OL.id = 5264
> and L.id = OL.link_id
> and LD.id = L.link_def_id;
>
> hierarchy_id
> --------------
> 1
> (1 row)
>
> i_hierarchy_id is declared as integer and is not used before this code nor
> as a column name anywhere.
>
> Does anyone have a clue what is going wrong? I use Postgresql 7.3.3 on
> FreeBSD 4.5.
>
try giving it a default of 0 in the declare statement, if that doesn't
work, post the whole code for the function.
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: plpgsql strangeness with select into <variable>
@ 2003-07-19 14:23 Philip Warner <pjw@rhyme.com.au>
parent: Reinoud van Leeuwen <reinoud@xs4all.nl>
2 siblings, 0 replies; 5+ messages in thread
From: Philip Warner @ 2003-07-19 14:23 UTC (permalink / raw)
To: Reinoud van Leeuwen <reinoud@xs4all.nl>; pgsql-hackers@postgresql.org
At 05:24 PM 18/07/2003 +0200, Reinoud van Leeuwen wrote:
>Does anyone have a clue what is going wrong? I use Postgresql 7.3.3 on
>FreeBSD 4.5.
Is it a before or after trigger. If 'before', are you trying to reference
data that does not exist yet?
----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 03 5330 3172 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2003-07-19 14:23 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-07-18 15:24 plpgsql strangeness with select into <variable> Reinoud van Leeuwen <reinoud@xs4all.nl>
2003-07-18 16:14 ` Josh Berkus <josh@agliodbs.com>
2003-07-18 16:19 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-07-18 17:55 ` Robert Treat <xzilla@users.sourceforge.net>
2003-07-19 14:23 ` Philip Warner <pjw@rhyme.com.au>
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