Обсуждение: How to set parameters in 'options'?

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?

:Stefan

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

-----Original Message-----
From: Stefan Keller [mailto:sfkeller@gmail.com] 
Sent: Tuesday, January 29, 2019 9:40 AM
To: Postgres General <pgsql-general@postgresql.org>
Subject: How to set parameters in 'options'?

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?

:Stefan

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

Use dynamic sql:
https://www.postgresql.org/docs/11/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

Regards,
Igor Neyman

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

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;

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

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

:Stefan

Am Di., 29. Jan. 2019 um 16:21 Uhr schrieb Adrian Klaver
<adrian.klaver@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.klaver@aklaver.com

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


-- 
Adrian Klaver
adrian.klaver@aklaver.com