The problem was the name of the temp table. When I created the view
inside a stored procedure the view was created "normally" and could be
referenced by the name I gave it. The temp tables can not be
referenced by the name I gave them. VB/ODBC did not produce an error
when I tried to open a recordset with the temp table name I used (ex.
table does not exist) it just did not open the recordset and continued
on. A little misleading.
I did find a work around posted by Richard Huxton/Bruce Momjian -
CREATE FUNCTION getpid () returns int4 as '/lib/libc.so.6' LANGUAGE
'C';
This returns the process id. The temp table named uses the process id
(pg_temp.PROCESS_ID.TEMP_TABLE_NUMBER) using the returned process id
and a counter in the VB program (the temp table is destroyed and
created multiple times) I was able to select using PostgreSQL
generated name.
Thank You to all that responded.
Bruce, Tom and everyone else involved with PostgreSQL,
This is my first experience with your database, despite a learning
curve I am going through right now I am throughly impressed with
Postgre. It is an awesome database.
Marc
