Foreign Table with Parameter in Query

Question

How can i use variables in a query of a foreign table? I could ALTER the option (query) of the foreign tabel and add the variable into the where clause but i am concerned that this locks the foreign table so no querys can be performed on it. I also thought about writing a procedure that dynamicly creates the whole foreign table but i dont want to create and delete the same foreign tabele with just some minor changes in its query.

Is there a better way of accomplishing my goal? I would like to call a function or procedure with the parameters "StartDate" / "EndDate" and get the results to the following

CREATE FOREIGN TABLE "A"."B.test"(
"Col1" character varying NULL,
"Col2" numeric NULL
)
SERVER "EXT"
OPTIONS(query '
SELECT ExternalCol1, ExternalCol2
FROM [DB1].[dbo].[Table1] A
WHERE A.ProdDate >= ''2000-01-01'' -- variable, something like StartDate
AND  A.ProdDate < ''2001-01-01'' -- variable something like EndDate
');

ALTER FOREIGN TABLE "A"."B.test"
OWNER TO postgres;

Answer 1

Since tds_fdw can push down WHERE conditions to the remote side, the solution would be to define the foreign table with table_name rather than query and set the match_column_names option on the foreign table. Then you can simply add the WHERE conditions to the query on the foreign table.

Answer 2

I ended up writing a function that creates the foreign table dynamicly and sets the parameter values of the query by using FORMAT. I added a random hash through the function to the foreign table so that the same foreign table could be used by differnt users. At the end of the function i delete the foreign table by its "random" id.

CREATE FUNCTION "A"."test"(startdate varchar, enddate varchar)
RETURNS TABLE (
"Col1" character varying,
"Col2" character varying
)
AS $$
DECLARE random_id VARCHAR := md5(random()::text);
DECLARE dynamic_query VARCHAR := '
CREATE FOREIGN TABLE "A"."%1$s"(
"Col1" character varying NULL,
"Col2" character varying NULL
)
SERVER "Server"
OPTIONS (query ''
    SELECT ExternalCol1, ExternalCol2
    FROM [DB1].[dbo].[Table1] A
    WHERE A.ProdDate >= ''''%2$s'''' AND  A.ProdDate < ''''%3$s''''
'');
ALTER FOREIGN TABLE "A"."%1$s"
OWNER TO postgres;';
BEGIN
EXECUTE FORMAT(dynamic_query, random_id, startdate, enddate);
RETURN QUERY EXECUTE FORMAT('SELECT * FROM "A"."%1$s"', random_id);
EXECUTE FORMAT('DROP FOREIGN TABLE "A"."%1$s"', random_id);
END
$$
LANGUAGE PLPGSQL