RE: [INTERFACES] ODBC: OID column in views

From: Dave Page <dpage(at)vale-housing(dot)co(dot)uk>
To: "'Byron Nikolaidis'" <byronn(at)solipsys(dot)com>
Cc: "'pgsql-interfaces(at)postgresql(dot)org'" <pgsql-interfaces(at)postgresql(dot)org>
Subject: RE: [INTERFACES] ODBC: OID column in views
Date: 1999-06-30 08:19:05
Message-ID: B9FE686F5C26D311AC22006097BCD3D65EFF@woody.vale-housing.co.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-interfaces

> -----Original Message-----
> From: Byron Nikolaidis [mailto:byronn(at)solipsys(dot)com]
> Sent: 29 June 1999 16:41
> To: Dave Page
> Cc: 'pgsql-interfaces(at)postgresql(dot)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.

Browse pgsql-interfaces by date

  From Date Subject
Next Message Moray McConnachie 1999-06-30 09:36:37 Re: [INTERFACES] ODBC and Access 2000: linked tables problem
Previous Message Reinaldo Crespo 1999-06-30 03:58:38 odbc & postgresql