Question 114739 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Foreign Table with Parameter in Query

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;
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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

  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search