Thread: plpgsql strangeness with select into
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_linkOL 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 OLwhere 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 __________________________________________________
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 |/