Re: How to set parameters in 'options'?

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.klaver@aklaver.com