From: | Stefan Keller <sfkeller(at)gmail(dot)com> |
---|---|
To: | Postgres General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: How to set parameters in 'options'? |
Date: | 2019-01-29 18:03:33 |
Message-ID: | CAFcOn2_LUxp6_KaQGGaZhbDQgVWtjagji-ktjrAm-XWbpGcVEQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Thomas Endres | 2019-01-29 18:24:41 | Re: Table Replication |
Previous Message | Jiří Fejfar | 2019-01-29 17:35:47 | parallel plpgsql function |