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.
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
<http://google.com>', 'yahoo.co.uk <http://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)*
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,
Kai Daguerre
FDW source: https://github.com/turbot/steampipe-postgres-fdw
Product page: https://steampipe.io
