Thread: dblink syntax question for remotely invoking void-returning procedures

Hello everyone,

I've got some questions about dblink that I couldn't find answers to in the documentation. Any help would be much appreciated.

I need to invoke a function on a remote server that returns either void or text, and I'm trying to find a nice way of doing it.

My test schema on the remote DB:

create table test (id serial, val text)
create or replace function inserttest() returns void as $$
begin
    insert into test (val) values ('x');
end;
$$ language 'plpgsql'

First I create a dblink connection to the above DB, then I try to call inserttest().

I've tried all the below queries in the console and in PL/pgSQL, two of which work correctly and *then* throw an error, which I think may be a bug.

From the console, this works:

select * from dblink('test', 'select inserttest()') as tmp(result text)

However, if I execute this within a PL/pgSQL procedure, the error message is

ERROR:  query has no destination for result data
HINT:  If you want to discard the results of a SELECT, use PERFORM instead.

Needless to say, I tried listening to hints and the perform doesn't work in this case.

The only result I've come up with is to modify the above function to "returns integer", always zero, and do this:

select result into junk from dblink('test', 'select inserttest()') as tmp(result integer)

This isn't ideal because a) it needs a junk variable, b) it's verbose and c) void methods must return useless information. Is there a better way to do the above in PL/pgSQL?

Many thanks in advance,

 - Ian

(for completeness' sake, and to prove I read the manual :-), the things I tried from the console and from procedures, and the results of each attempt follows).

--WORKS, but throws error
select * from dblink('test', 'select inserttest()') as t1(test void);
 -> column "test" has pseudo-type void

--WORKS, but throws error
select * from dblink('test', 'select inserttest()') as t1(test text);
 -> query has no destination for result data

perform dblink('test', 'select inserttest()');
 -> function returning record called in context that cannot accept type record

select dblink('test', 'select inserttest()');
 -> function returning record called in context that cannot accept type record

select * from dblink('test', 'select inserttest()');
 -> a column definition list is required for functions returning "record"

select * from dblink('test', 'select inserttest()') as void;
 -> a column definition list is required for functions returning "record"

perform dblink_exec('test', 'select inserttest()');
 -> statement returning results not allowed

perform dblink('test', 'select inserttest()');
  -> ERROR:  function returning record called in context that cannot accept type record
 -> CONTEXT:  SQL statement "SELECT  dblink('test', 'select inserttest()')"
 
perform dblink('test', 'perform inserttest()');
 -> ERROR:  sql error
 -> DETAIL:  ERROR:  syntax error at or near "perform"
 -> LINE 1: perform inserttest()

perform dblink('test', 'inserttest()');
 -> ERROR:  sql error
 -> DETAIL:  ERROR:  syntax error at or near "inserttest"
 -> LINE 1: inserttest()
 
select dblink_exec('test', 'perform inserttest()');
 -> ERROR: sql error
 -> SQL state: 42601
 -> Detail: ERROR:  syntax error at or near "perform"
 -> LINE 1: perform inserttest()
 

On 1/24/09, Ian Sollars <ian.sollars@gmail.com> wrote:
> Hello everyone,
>
> I've got some questions about dblink that I couldn't find answers to in the
> documentation. Any help would be much appreciated.
>
> I need to invoke a function on a remote server that returns either void or
> text, and I'm trying to find a nice way of doing it.
>

did you try this?
perform * from dblink('test', 'select inserttest()') as t1(test text);

Anyways, I never write void returning functions.  Another problem with
them (for example) is that they can't be called using the binary query
protocol.  These are minor nits since the workaround is easy.

merlin

Take a stab at plproxy if you want to remotly call functions. Should be much better suited than dblink for that.

Regards
Asko

Thank you, Merlin and Asko for your responses  :-) Because my whole project is using PL/Proxy I should have realised I could use that instead of dblink in this case.

Thanks again,

 - Ian