plpgsql strangeness with select into <variable>

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

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.

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

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 |/