Re: dblink_exec: can it perform a remote function?

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(dot)klaver(at)aklaver(dot)com