Date: Fri, 18 Jul 2003 17:24:52 +0200
From: Reinoud van Leeuwen <reinoud@xs4all.nl>
To: pgsql-hackers@postgresql.org
Subject: plpgsql strangeness with select into <variable>
Message-ID: <20030718172452.L54220@spoetnik.xs4all.nl>
Mail-Followup-To: pgsql-hackers@postgresql.org
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
User-Agent: Mutt/1.2.5.1i

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
__________________________________________________