Re: How to set parameters in 'options'?

[Adrian Klaver]

On 1/29/19 10:03 AM, Stefan Keller wrote:

Many thanks to Igor and Adrian for your hints.

Got it to work like this:

create function link_server(_server text, _host text, _port text, _dbname text)
returns void as $$
begin
   execute format('create server %s
     foreign data wrapper postgres_fdw
     options (host %L, port %L, dbname %L)', _server, _host, _port, _dbname);
end;
$$ language plpgsql;

I would go with %I instead of %s for _server.

select link_server('other_db_server', '111.11.11.11', '5432', 'other_db');

But I actually hoped being able avoid "execute"...

Why?

:Stefan

Am Di., 29. Jan. 2019 um 16:21 Uhr schrieb Adrian Klaver
<adrian.kla...@aklaver.com>:

On 1/29/19 6:40 AM, Stefan Keller wrote:

Hi,

I'd like to write a function like this:

create function foo(_host text, _port text, _dbname text)
returns void as $$
    create server _server
      foreign data wrapper postgres_fdw
      options (host _host, port _port, dbname _dbname);
      -- ERROR: syntax error at or near "_host"
$$ language sql;

In order to e.g. do:
select foo('111.11.11.11', '5432', 'mydb');

How can I set the parameters in 'options' using those variables?

If you are going to use plpgsql, then format:

https://www.postgresql.org/docs/10/functions-string.html#FUNCTIONS-STRING-FORMAT

https://www.postgresql.org/docs/10/plpgsql-statements.html#PLPGSQL-QUOTE-LITERAL-EXAMPLE

So something like:

execute format('create server _server
      foreign data wrapper postgres_fdw
      options (host $1, port $2, dbname $3') USING _host, _port, _dbname

Not sure where the _server is coming from so that might have to be dealt
with.

:Stefan

P.S. Actually I'll put this code in a plggsql function later on.

--
Adrian Klaver
adrian.kla...@aklaver.com

--
Adrian Klaver
adrian.kla...@aklaver.com