PostgreSQL: Create foreign table with string and variable

Question

My problem: I need to define foreign table dynamically and set different where conditions every time. I am doing this in function, but I am getting error which doesn't make sense to me during creation of the foreign table(via oracle_fdw).

Creation of foreign table that works:

CREATE FOREIGN TABLE MYFOREIGNTABLE 
(
column1 int,
column2 text
)
SERVER fwdb
OPTIONS (table $$(
  select
    column1,
    column2
  from
    table1
  where 
   column3 = 5
   and column4 = 'a'
)$$);

Now if I try to split the string for putting there my variables (instead of variable I left there number so anybody can try it), it stop working and I am getting error

[Code: 0, SQL State: 42601] ERROR: syntax error at or near "||"

CREATE FOREIGN TABLE MYFOREIGNTABLE 
(
column1 int,
column2 text
)
SERVER fwdb
OPTIONS (table $$(
  select
    column1,
    column2
  from
    table1
  where 
   column3 = $$ || 5 || $$
   and column4 = 'a'
)$$);

Just for sure I tried my string in select to make sure I didn't do any syntax mistake and it works no problem

select $$(
  select
    column1,
    column2
  from
    table1
  where 
   column3 = $$ || 5 || $$
   and column4 = 'a'
)$$

I tried few other things like using concat() or putting my whole string into variable OPTIONS (table myvariable); But neither worked. What is the correct syntax here?

PostgreSQL 11.10 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit

Which foreign data wrapper are you using? oracle_fdw?

Laurenz Albe
– Laurenz Albe

2020-12-02 09:12:46 +00:00
Commented Dec 2, 2020 at 9:12 — Laurenz Albe
– Laurenz Albe, Commented Dec 2, 2020 at 9:12
yes it is oracle_fdw to connect to oracle database

Baker
– Baker

2020-12-02 09:32:40 +00:00
Commented Dec 2, 2020 at 9:32 — Baker
– Baker, Commented Dec 2, 2020 at 9:32

Laurenz Albe · Accepted Answer · 2020-12-02 09:23:35Z

2

You have to use a string literal as value for a FDW option, expressions like the string concatenation you are trying to use are not allowed.

You will have to construct the complete statement with dynamic SQL, for example

DO
$$DECLARE
   var integer := 5;
BEGIN
   EXECUTE
      format(
         E'CREATE FOREIGN TABLE MYFOREIGNTABLE (\n'
         '   column1 int,\n'
         '   column2 text\n'
         ') SERVER fwdb OPTIONS (\n'
         '   table ''(SELECT column1,\n'
         '                   column2\n'
         '            FROM table1\n'
         '            WHERE column3 = %s\n'
         '              AND column4 = ''''a'''')'')',
         var
      );
END;$$;

For string variables you have to get the quoting right by using quote_literal(quote_literal(var)).

answered Dec 2, 2020 at 9:23

Laurenz Albe

257k22 gold badges314 silver badges390 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Baker Over a year ago

Thank you, it works, but could you please explain me what does E at the beginning means? And also is it possible to substitute ' for $$ as a string quoting?

Laurenz Albe Over a year ago

The E is an "extended string literal", and I use it only so I can write \n for newline. You can write the whole query in a single line if you want. Sure, you can use dollar quoting, but you need to use different quotes from the surrounding $$, like for example $query$ . That might be better, because you then have only one level of double quotes.

Collectives™ on Stack Overflow

PostgreSQL: Create foreign table with string and variable

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related