Hi!
(First post. If this is not the appropriate list, please feel free to move or let me know. )
I am developing an FDW which allows various data sources to act as virtual tables, allowing various different APIs to be queried using a consistent SQL interface - a similar concept to Osquery but using Postgres instead of SQLite. It is working pretty well, but we have hit a bit of a roadblock (or bump in the road at least).
We often have virtual tables where a list operation is not viable/possible without providing quals. For example we have implemented a 'whois' table, which will retrieve whois information for specified domains. It is clearly not practical to do an unqualified 'list' of all domains.
(First post. If this is not the appropriate list, please feel free to move or let me know. )
I am developing an FDW which allows various data sources to act as virtual tables, allowing various different APIs to be queried using a consistent SQL interface - a similar concept to Osquery but using Postgres instead of SQLite. It is working pretty well, but we have hit a bit of a roadblock (or bump in the road at least).
We often have virtual tables where a list operation is not viable/possible without providing quals. For example we have implemented a 'whois' table, which will retrieve whois information for specified domains. It is clearly not practical to do an unqualified 'list' of all domains.
The problem we have is that the results of nested subqueries/joins are not being passed as quals to the outer query.
So for example
select * from whois_domain where domain in ('google.com', 'yahoo.co.uk')
So for example
select * from whois_domain where domain in ('google.com', 'yahoo.co.uk')
works fine, and a qual is passed to the fdw with a value of ['google.com', 'yahoo.co.uk']
However the following (assuming a 'domains table containing required domains) does not work:
select * from whois_domain where domain in (select domain from domains)
However the following (assuming a 'domains table containing required domains) does not work:
select * from whois_domain where domain in (select domain from domains)
In this case, no quals are passed to the fdw, so the select * from whois_domain query therefore fails. What we would like is to ensure the subquery runs first, and for the results to be available to the outer query.
---
Using SQLite, this could be accomplished using cross-joins (https://sqlite.org/optoverview.html#crossjoin). Is there an equivalent (or similar) mechanism in Postgres to ensure query ordering?
Within the FDW, I have tried using the GetForeignPaths function to return a path which returns a single row when the 'key' column is used. This does provide a qual, however it is of type T_Var - I believe I need a constant qual.
Any suggestion welcome as to either a different way to structure the query or whether the FDW can request/enforce the ordering by returning specific planning results.
Many thanks,
---
Using SQLite, this could be accomplished using cross-joins (https://sqlite.org/optoverview.html#crossjoin). Is there an equivalent (or similar) mechanism in Postgres to ensure query ordering?
Within the FDW, I have tried using the GetForeignPaths function to return a path which returns a single row when the 'key' column is used. This does provide a qual, however it is of type T_Var - I believe I need a constant qual.
Any suggestion welcome as to either a different way to structure the query or whether the FDW can request/enforce the ordering by returning specific planning results.
Many thanks,
Kai Daguerre
FDW source: https://github.com/turbot/steampipe-postgres-fdw
Product page: https://steampipe.io
