Thread: dblink_exec: can it perform a remote function?

dblink_exec: can it perform a remote function?

From

Thiemo Kellner

Date:

22 March 2016, 11:34:40

Hi all

I am trying to execute a function on a remote server using dblink to
test stuff.

My function is:
create or replace function /*LOGGER.*/WARN(in P_MESSAGE text)
    returns void as
$warn$
begin
       raise debug     '%', P_MESSAGE;
       raise log       '%', P_MESSAGE;
       raise info      '%', P_MESSAGE;
       raise notice    '%', P_MESSAGE;
       raise warning   '%', P_MESSAGE;
end;
$warn$ language PLPGSQL;

I try to execute it with:
do language PLPGSQL
$anonymous$
begin
       set session client_min_messages to log;
       perform dblink_connect_u('pragma','dbname=thiemo password=XXX');
       perform dblink_exec('pragma','perform WARN(P_MESSAGE := ''Raise
dblink test'');');
       perform dblink_exec('pragma','commit;');
       perform dblink_disconnect('pragma');
end;
$anonymous$

I get the error:
psql:test_02.sql:22: ERROR:  syntax error at or near "perform"
CONTEXT:  Error occurred on dblink connection named "pragma": could not
execute command.
SQL statement "SELECT dblink_exec('pragma','perform WARN(P_MESSAGE :=
''Raise dblink test'');')"
PL/pgSQL function inline_code_block line 12 at PERFORM

If I try to execute it with:
do language PLPGSQL
$anonymous$
begin
       set session client_min_messages to log;
       perform dblink_connect_u('pragma','dbname=thiemo password=XXX');
       perform dblink_exec('pragma','select WARN(P_MESSAGE := ''Raise
dblink test'');');
       perform dblink_exec('pragma','commit;');
       perform dblink_disconnect('pragma');
end;
$anonymous$

Isn't it possible to execute funtions through dblink? Any hint is
appreciated.

Kind regards

Thiemo

--
Auf Gelassene Pferde kann man bauen!
+49 (0)1578-772 37 37
+41 (0)78 947 36 21
SIP/iptel.org: thiemo.kellner
http://www.gelassene-pferde.biz
Mitglied bei http://www.keep-it-natural.org
Öffentlicher PGP-Schlüssel:
http://pgp.mit.edu/pks/lookup?op=get&search=0x8F70EFD2D972CBEF

Attachment

thiemo.vcf

Re: dblink_exec: can it perform a remote function?

From

Thiemo Kellner

Date:

22 March 2016, 11:40:40

Oh, I just noticed, I forgot to post the error message of the second
try. This one, I can understand.
psql:test_02.sql:21: ERROR:  statement returning results not allowed
CONTEXT:  SQL statement "SELECT dblink_exec('pragma','select
WARN(P_MESSAGE := ''Raise dblink test'');')"

On 22.03.2016 12:33, Thiemo Kellner wrote:
> Hi all
>
> I am trying to execute a function on a remote server using dblink to
> test stuff.
>
> My function is:
> create or replace function /*LOGGER.*/WARN(in P_MESSAGE text)
>     returns void as
> $warn$
> begin
>        raise debug     '%', P_MESSAGE;
>        raise log       '%', P_MESSAGE;
>        raise info      '%', P_MESSAGE;
>        raise notice    '%', P_MESSAGE;
>        raise warning   '%', P_MESSAGE;
> end;
> $warn$ language PLPGSQL;
>
> I try to execute it with:
> do language PLPGSQL
> $anonymous$
> begin
>        set session client_min_messages to log;
>        perform dblink_connect_u('pragma','dbname=thiemo password=XXX');
>        perform dblink_exec('pragma','perform WARN(P_MESSAGE := ''Raise
> dblink test'');');
>        perform dblink_exec('pragma','commit;');
>        perform dblink_disconnect('pragma');
> end;
> $anonymous$
>
> I get the error:
> psql:test_02.sql:22: ERROR:  syntax error at or near "perform"
> CONTEXT:  Error occurred on dblink connection named "pragma": could not
> execute command.
> SQL statement "SELECT dblink_exec('pragma','perform WARN(P_MESSAGE :=
> ''Raise dblink test'');')"
> PL/pgSQL function inline_code_block line 12 at PERFORM
>
> If I try to execute it with:
> do language PLPGSQL
> $anonymous$
> begin
>        set session client_min_messages to log;
>        perform dblink_connect_u('pragma','dbname=thiemo password=XXX');
>        perform dblink_exec('pragma','select WARN(P_MESSAGE := ''Raise
> dblink test'');');
>        perform dblink_exec('pragma','commit;');
>        perform dblink_disconnect('pragma');
> end;
> $anonymous$
>
> Isn't it possible to execute funtions through dblink? Any hint is
> appreciated.
>
> Kind regards
>
> Thiemo
>
>
>
>

--
Auf Gelassene Pferde kann man bauen!
+49 (0)1578-772 37 37
+41 (0)78 947 36 21
SIP/iptel.org: thiemo.kellner
http://www.gelassene-pferde.biz
Mitglied bei http://www.keep-it-natural.org
Öffentlicher PGP-Schlüssel:
http://pgp.mit.edu/pks/lookup?op=get&search=0x8F70EFD2D972CBEF

Attachment

thiemo.vcf

Re: dblink_exec: can it perform a remote function?

From

Adrian Klaver

Date:

22 March 2016, 14:58:40

On 03/22/2016 04:39 AM, Thiemo Kellner wrote:
> Oh, I just noticed, I forgot to post the error message of the second
> try. This one, I can understand.
> psql:test_02.sql:21: ERROR:  statement returning results not allowed
> CONTEXT:  SQL statement "SELECT dblink_exec('pragma','select
> WARN(P_MESSAGE := ''Raise dblink test'');')"


http://www.postgresql.org/docs/9.5/static/contrib-dblink-exec.html

"dblink_exec executes a command (that is, any SQL statement that doesn't
return rows) in a remote database."


What I think you are looking for:

http://www.postgresql.org/docs/9.5/static/contrib-dblink-function.html

"dblink executes a query (usually a SELECT, but it can be any SQL
statement that returns rows) in a remote database."

>
> On 22.03.2016 12:33, Thiemo Kellner wrote:
>> Hi all
>>
>> I am trying to execute a function on a remote server using dblink to
>> test stuff.
>>
>> My function is:
>> create or replace function /*LOGGER.*/WARN(in P_MESSAGE text)
>>     returns void as
>> $warn$
>> begin
>>        raise debug     '%', P_MESSAGE;
>>        raise log       '%', P_MESSAGE;
>>        raise info      '%', P_MESSAGE;
>>        raise notice    '%', P_MESSAGE;
>>        raise warning   '%', P_MESSAGE;
>> end;
>> $warn$ language PLPGSQL;
>>
>> I try to execute it with:
>> do language PLPGSQL
>> $anonymous$
>> begin
>>        set session client_min_messages to log;
>>        perform dblink_connect_u('pragma','dbname=thiemo password=XXX');
>>        perform dblink_exec('pragma','perform WARN(P_MESSAGE := ''Raise
>> dblink test'');');
>>        perform dblink_exec('pragma','commit;');
>>        perform dblink_disconnect('pragma');
>> end;
>> $anonymous$
>>
>> I get the error:
>> psql:test_02.sql:22: ERROR:  syntax error at or near "perform"
>> CONTEXT:  Error occurred on dblink connection named "pragma": could not
>> execute command.
>> SQL statement "SELECT dblink_exec('pragma','perform WARN(P_MESSAGE :=
>> ''Raise dblink test'');')"
>> PL/pgSQL function inline_code_block line 12 at PERFORM
>>
>> If I try to execute it with:
>> do language PLPGSQL
>> $anonymous$
>> begin
>>        set session client_min_messages to log;
>>        perform dblink_connect_u('pragma','dbname=thiemo password=XXX');
>>        perform dblink_exec('pragma','select WARN(P_MESSAGE := ''Raise
>> dblink test'');');
>>        perform dblink_exec('pragma','commit;');
>>        perform dblink_disconnect('pragma');
>> end;
>> $anonymous$
>>
>> Isn't it possible to execute funtions through dblink? Any hint is
>> appreciated.
>>
>> Kind regards
>>
>> Thiemo
>>
>>
>>
>>
>
>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com