Thread: Re: [HACKERS] plpgsql strangeness with select into
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.