RE: [INTERFACES] ODBC: OID column in views

> -----Original Message-----
> From: Byron Nikolaidis [mailto:byronn@solipsys.com]
> Sent: 29 June 1999 16:41
> To: Dave Page
> Cc: 'pgsql-interfaces@postgresql.org'
> Subject: Re: [INTERFACES] ODBC: OID column in views
> 
<SNIP>
> 
> Ahhhh,  I see now.  DAO is calling SQLColumns() on the 
> "pg_tables" table.  Since
> the driver realizes it is a system table (i.e., because it 
> begins with pg_), it
> automatically throws the OID column in there.  There is no 
> option to turn that
> off and it is not intelligent enough to check whether it is a view.

This occurs whether it is a system view/table or not. I executed the
following this morning:

CREATE VIEW fred AS SELECT seckey, seclevel FROM sys_userpriv;
SELECT * FROM fred;

sys_userpriv is an existing populated table on my database. I got the same
error as previously:

conn=39272764, query='SELECT "oid" ,"seckey" ,"seclevel"  FROM "fred" '
ERROR from backend during send_query: 'ERROR:  system column oid not
available - fred is a view'
STATEMENT ERROR: func=SC_execute, desc='', errnum=1, errmsg='Error while
executing the query'


> The idea was that when looking at System Tables, you would 
> always be interested
> in the oid column so the driver should show it.

Which makes sense...

> Wait a minute, I don't remember any "pg_tables" view???  Is 
> that really a system
> table, or something you created?  If its something you 
> created, you can just not
> start it with "pg_" and problem solved!

It appeared with 6.4 if I remember correctly, along with pg_indexes,
pg_rules, pg_user and pg_views.

> Another possibility is to use SQLTables() meta function to 
> get the tables
> instead of using a low level query.  Theoretically, it would 
> be more portable.

That's a little more complex than I want to get. One of the main places this
problem has cropped up is in the SQL function of pgAdmin - it's just a
textbox where the user can type and execute SQL on the fly. Obviously I want
to try to avoid having to parse the entered SQL myself when DAO, ODBC and
the backend are already doing it.

Regards,

Dave.