Adding autoincrement/hash column to view

[gioppoluca]

Is it possible to add an autoincrement column to a view table?

Or a column made from a hash of an existing column to have a numerial index instead of a string.

The problem is having sometime CSV files that do not have ID thus PK but could have unique value columns that could be used as PK.

Using that column as PK can be sometimes unconvinient for example when using odata and using a string as PK/ID

Having either an autoincrement (that could have the problem of not being the same over time) or a hash of a column (better since it will be always the same)  could expose a better data to outside.

Thanks

Luca

[rareddy]

auto increment on view will not work, as there is no where to store in Teiid and return it consistently next time or time after that. Hashing might work, you can define hashing UDF based on some other key in the view that you know going to be consistent always, and wrap that resultant column in a PK.

Ramesh..

[shawkins]

XMLTABLE allows a FOR ORDINALITY column to assign each row a number, which could be added to TEXTTABLE.  There is also the ROWNUMBER() analytical function if a number should be based upon an ordering (although performance may be an issue unless materialized).  We don't have a built-in hash function for this purpose and care would need to be taken in a UDF to ensure the probability of collisions is low for it to function properly as a pk.

[rareddy]

Steve,

When ORDINALITY or ROWNUMBER is used would user able to issue a follow on query to select a particular row from the resultset?

Ramesh..

[shawkins]

Ramesh,

Yes each provides a unique identifier for a row.  FOR ORDINALITY is better performing in that it is associated with the results prepositionally as it loads.  ROWNUMBER requires the entire result to be sorted regardless.  However neither is entirely stable - if the data set changes the ordinal number will likely change, and unless the rownumber is over something like a create timestamp as data changes so too will the numbering.

Steve

[rareddy]

Logged [TEIID-2859] Provide identity generation for TEXTTABLE and OBJECTTABLE - JBoss Issue Tracker