0

Is there a way to query the OIDs in MonetDB using SQL? That is, I'd like to do something along the lines of 

SELECT <oid>, <column 1>, ..., <column N> FROM <table>

and 

SELECT * FROM <table> WHERE <oid> IN (...)

I looked through the documentation and source examples, but found no mention to querying OIDs or even if the OIDs are accessible in MonetDB/SQL.

TR.
  • 200
  • 1
  • 7

1 Answers1

-1

I don't think you can access the OID's directly through the SQL interface, since they are for internal use only. However, you can get row identifiers, which might do what you are looking for. See Window functions for more information.

So you would get something like

SELECT ROW_NUMBER over () AS row, , ..., FROM

What do you need it for?

rcijvat
  • 182
  • 2
  • 12
  • I'm building a [foreign data wrapper](https://wiki.postgresql.org/wiki/Foreign_data_wrappers) in PostgreSQL to read and write MonetDB tables. From my understanding, the Postgres FDWs rely on having a column that uniquely identifies a record within a transaction (in fact I'm looking for an equivalent to Posgtgres [ctid](http://www.postgresql.org/docs/9.4/static/ddl-system-columns.html)). OIDs would solve my issue trivially if I could access those from the SQL interface. – TR. Jul 07 '14 at 03:10
  • Also, note that there is a [MonetDB FDW](https://wiki.postgresql.org/wiki/Foreign_data_wrappers#MonetDB_FDW) for postgres already but it lacks several of the FDW features to be useful. – TR. Jul 07 '14 at 03:14
  • I see, well every well designed database schema has defined a primary key on every table, which could span multiple columns. So you could use these as your identifiers. If it **has** to be a single column, and there is no such column yet, you could create it at runtime using the ROW_NUMBER() functionality that I described. – rcijvat Jul 08 '14 at 06:25
  • Unfortunately I cannot build an extension that fails in the case when there is no primary key in a table. I don't think I can use the workaround you suggested, though -- I need to identify a record on a table using some unique id, not a record's relative position within a result set. – TR. Jul 10 '14 at 20:29
  • You could attach row_number as a column to the initial table, hence it would behave exactly the same as when it would be a primary key in a single query. You will not have any guarantee that the outcome will be the same for different queries of course. – rcijvat Jul 14 '14 at 07:20