Many thanks for the fast response. Using an SRF is an interesting idea, I'll have a play and see if we can make that work.
Cheers, Kai On Wed, Jan 27, 2021 at 3:27 PM Tom Lane <t...@sss.pgh.pa.us> wrote: > Kai Daguerre <k...@turbot.com> writes: > > 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. > > In that case you're going to have to resign yourself to some queries > failing. This is unavoidable, consider "select * from whois". But > just because the query has a WHERE condition doesn't mean that a useful > restriction clause can be extracted for any particular table. > > I think the best you can do is (1) fail at runtime if there's no qual > and (2) at plan time, return an extremely high cost estimate for a > qual-less scan, in hopes of discouraging the planner from choosing > that. (Instead of (2), you could perhaps not generate a scan path > at all, but that's likely to lead to an unintelligible error message.) > > Perhaps you should rethink whether you really want a foreign table > rather than a set-returning function. With the SRF approach it's > automatic that the user must supply the restricting argument(s) you need. > > regards, tom lane >