search

tds-fdw / tds_fdw

A PostgreSQL foreign data wrapper to connect to TDS databases (Sybase and Microsoft SQL Server)
Other
381 stars 102 forks source link

Dynamic queries? #201

Closed nicklaich closed 5 years ago

nicklaich commented 5 years ago

Instead of editing pg_foreign_table every time you need to change a query, something like this could be nice:

CREATE FOREIGN TABLE mssql_customers( name varchar, age int) SERVER mssql_svr OPTIONS (query_dynamic, row_estimate_method 'showplan_all'); SET tds_fdw.query_text='select name, age from sales.coustomers where age>10'; SELECT * from mssql_customers;

SudoerWithAnOpinion commented 5 years ago

What exactly do you need to change in this query to make it dynamic? Generally speaking, you don’t need to change the query text, just the WHERE clause in the query “above”.

-SNIP-

nicklaich commented 5 years ago

Hi! Generally speaking, i do, because queries can be rather complex, and include joins and withs. Though, i do agree with result structure is being constant.

SudoerWithAnOpinion commented 5 years ago

Apologies if I was unclear... What needs to be dynamic exactly? You provided an example with query text but did not indicate how it would change.

You can always use a plpgsql function to handle modifying the foreign table.

nicklaich commented 5 years ago

Well, the query could transform into this, for example: 'select name, max(quantity) from sales.vip_customers c join sales.orders o where c.id=o.customer where left(o.item_serial,7)=''es1-521'' group by name'

in common, any query that returns the same table structure.

Thing is, ATM, queries is the only option to pass filtering and query data to mssql. And i not sure how modifying system tables could work in multi-user environment.

Well, by your questions i guess the topic is not the thing you expect from tds_fdw. Maybe i should poke around dblink_tds instead. Sorry for taking your time.

SudoerWithAnOpinion commented 5 years ago

It looks like there is a disconnect between what a query is versus a table. TDS_FDW is supposed to provide data from another server's table (or view). The query option is so you can use a query as your data source, instead of having to create a view, not to modify each time you need different data.

It also looks like you are looking for query pushdown (eg. sending the WHERE filters to the foreign server) which is currently an alpha feature.