Support system versioning for views

[knutwannheden]

SQL Server supports querying views with the AS OF clause and SQL Server will transparently apply the clause to all temporal tables participating in the view definition's query. See https://docs.microsoft.com/en-us/sql/relational-databases/tables/querying-data-in-a-system-versioned-temporal-table?view=sql-server-ver15#using-views-with-as-of-sub-clause-in-temporal-queries for details.

I don't know if this feature is defined by the SQL standard or not, but it looks very powerful indeed.

[xocolatl]

That would be a really nice feature to have in core PostgreSQL. I don't see how this extension can do it without hindering more than helping. Also, what should happen if the view uses an AS OF clause in its query? Should the AS OF on the view override it, or somehow combine them, or what?

The standard does not allow system versioning on anything but regular persistent base tables.

If you have any workable ideas on how it could be done, I'll be happy to review your patch or write one myself.

[knutwannheden]

I was just looking for a way to define a point in time in a single place using which all temporal tables will be queried. Oracle's flashback technology provides some mechanism like this, IIRC (but I may well be wrong about this, since it is quite some time ago I worked with Oracle flashback). Then I stumbled across this SQL Server feature, which looks really nice and thought that this would be nice for PostgreSQL and I couldn't find a better place to report this than here.

I could of course try out how SQL Server behaves if it already has an AS OF clause on some of the tables it selects from and report my results back here. But that on its own isn't very valuable, I guess...

[knutwannheden]

FWIW: I did perform the test now and there is an error reported when I execute the query if the view contains an AS OF clause already: SQL Error [13590] [S0001]: Temporal FOR SYSTEM_TIME clause can only be set once per temporal table. 'example' has more than one temporal FOR SYSTEM_TIME clause.

[knutwannheden]

The Oracle feature I was referring to is the DBMS_FLASHBACK PL/SQL package (see https://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_flashback.htm#ADFNS01009). It provides procedures like ENABLE_AT_TIME.

[knutwannheden]

MariaDB also appears to allow querying views with the AS OF clause: https://mariadb.com/kb/en/system-versioned-tables/#views-and-subqueries

Also it offers a system variable offering a feature equivalent to that of Oracle's stored procedure: https://mariadb.com/kb/en/system-versioned-tables/#views-and-subqueries

[df7cb]

I'll just drop that here without comment. :)

https://www.postgresql.org/docs/6.3/c0503.htm