Mailbox-Line: From mark@somanetworks.com  Wed Jul  3 19:20:51 2002
Date: Wed, 3 Jul 2002 16:59:03 -0400
From: Mark Frazer <mark@somanetworks.com>
To: pgsql-sql@postgresql.org, pgsql-interfaces@somanetworks.com
Subject: Possible Bug regarding temp tables (sql or psql?)
Message-ID: <20020703165903.A17844@somanetworks.com>
Mime-Version: 1.0
Content-Type: multipart/mixed; boundary="T4sUOijqQbZv57TR"
Content-Disposition: inline
User-Agent: Mutt/1.2.5.1i
Organization: Detectable, well, not really
Resent-From: mark@somanetworks.com
Resent-Date: Wed, 3 Jul 2002 17:01:21 -0400
Resent-To: pgsql-interfaces@postgresql.org
Resent-Message-Id: <20020703210121.A585B23B43@frogger.yyz.somanetworks.com>

--T4sUOijqQbZv57TR
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

When using the attached script in psql, the temp variables disappear as 
far as \distv shows, but running the script a second time fails.

To reproduce, save the following script as bug.sql, then start psql on an
test database.
\i bug.sql
\distv
-- no relations should be shown
\i bug.sql 
-- this will fail.

However, if you run psql again, you can
\i bug.sql
successfully.  It will only fail if run twice in the same script.

cheers
-mark

-- 
Hardy Boys: too easy. Nancy Drew: too hard! - Fry

--T4sUOijqQbZv57TR
Content-Type: text/plain; charset=us-ascii
Content-Disposition: attachment; filename="bug.sql"

CREATE TABLE foo (
	foo_idx SERIAL PRIMARY KEY,
	foo INTEGER ) ;
CREATE TABLE bar (
	bar_idx SERIAL PRIMARY KEY,
	foo_idx INTEGER REFERENCES foo,
	bar INTEGER ) ;
INSERT INTO foo ( foo ) VALUES ( 111 ) ;
INSERT INTO foo ( foo ) VALUES ( 222 ) ;
INSERT INTO foo ( foo ) VALUES ( 333 ) ;

BEGIN WORK ;
SELECT foo_idx INTO TEMP v_idx FROM foo WHERE foo.foo = 222 ;
INSERT INTO bar ( foo_idx, bar ) VALUES ( v_idx.foo_idx, 888 ) ;
END WORK ;

DROP TABLE foo ;
DROP SEQUENCE foo_foo_idx_seq ;
DROP TABLE bar ;
DROP SEQUENCE bar_bar_idx_seq ;

--T4sUOijqQbZv57TR--