X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [64.117.225.181])
	by svr1.postgresql.org (Postfix) with ESMTP id 3142CCC00C
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 18 Jul 2003 16:15:41 +0000 (GMT)
Received: from svr1.postgresql.org ([64.117.224.193])
	by localhost (svr5.postgresql.org [64.117.225.181]) (amavisd-new,
	port 10024) with ESMTP id 39887-06
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 18 Jul 2003 16:16:03 +0000 (GMT)
Received: from davinci.ethosmedia.com (unknown [209.10.40.251])
	by svr1.postgresql.org (Postfix) with ESMTP id 74063CCD62
	for <pgsql-sql@postgresql.org>; Fri, 18 Jul 2003 13:15:29 -0300 (ADT)
Received: from [63.195.55.98] (HELO spooky)
	by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2)
	with ESMTP id 3228733; Fri, 18 Jul 2003 09:16:11 -0700
Content-Type: text/plain;
  charset="iso-8859-1"
From: Josh Berkus <josh@agliodbs.com>
Organization: Aglio Database Solutions
To: Reinoud van Leeuwen <reinoud@xs4all.nl>, pgsql-sql@postgresql.org
Subject: Re: [HACKERS] plpgsql strangeness with select into <variable>
Date: Fri, 18 Jul 2003 09:14:43 -0700
User-Agent: KMail/1.4.3
References: <20030718172452.L54220@spoetnik.xs4all.nl>
In-Reply-To: <20030718172452.L54220@spoetnik.xs4all.nl>
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Message-Id: <200307180914.43379.josh@agliodbs.com>
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200307/255
X-Sequence-Number: 14369

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