Support all ODBC SQL

[pramsey]

The current implementation seems to take a very gentle approach to pushing filters to remote, only taking the first qual off the list. It might be nicer to properly deparse the qual tree and write out those pieces of SQL that are considered legal for use in ODBC queries.

https://docs.microsoft.com/en-us/sql/odbc/reference/appendixes/sql-minimum-grammar

[pramsey]

@rafatower, do we have enough users of FDW connectors, particularly using a WHERE clause with more than one qual, for this to be helpful?

[rafatower]

I'll compile a list of users and gather some info from the metrics we have.

[pramsey]

Related to #75, the handling of quals in the current code doesn't seem to actually do anything, probably because it's trying to extract them from the PlanState rather than the Plan. Fixing it would require much of the work that this issue also requires, in terms of deparsing the expression tree into ODBC SQL, and moving that work into an earlier stage of the FDW API (basically, aping the way the OGR FDW and postgres_fdw do it)

[thoughtchad]

+1000

I primarily used odbc_fdw to extract bulk data from a odbc source into postgres. I understand the true nature of fdw is to be able to join fdw tables, etc... which requires full parsing.

I think a quick win for odbc_fdw, would be to provide a fdw option that basically provides a static 'where' clause that will always get pushed to the remote.

example without static where:
select count(*) from odbc_fdw_tbl1
-- 100,000

example with static where set to 'dt_created >= date '2015-01-01':
select count(*) from odbc_fdw_tbl1
-- 25,000

[pramsey]

@thoughtchad doesn't the sql_query option already provide essentially this functionality?

[trptech]

@pramsey As I recall, the sql_query is only used for the import foreign schema. I use it extensively in data integrations. It basically allows one to import the schema automagically without all the fuss of create table and mapping data types. I use it like this: "select cola, colb, colc from the_table where 1=0" - Which provides an very fast schema import, with no data. After much testing, even the where clause on the sql_options is very strange in how it parses it out... 1=0 get's passed to the remote db.... anything else triggers a full remote table query back to the postgres instance - and then the where clause is applied.

I've followed the code through, and as soon as it hits the tree parsing - I'm a goner.
Pretty sure the sql_option goes through the parser just like any other query would - but I could be wrong.

A sql_where_static option is essentially what I'm looking for.

Happy to help in any way I'm able to.