dblink syntax question for remotely invoking void-returning procedures

From: Ian Sollars <ian(dot)sollars(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: dblink syntax question for remotely invoking void-returning procedures
Date: 2009-01-24 16:23:09
Message-ID: 45df74df0901240823hc971fdfr71169f440604ebf6@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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()

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ivan Sergio Borgonovo 2009-01-24 17:24:29 Re: very long update gin index troubles back?
Previous Message Teodor Sigaev 2009-01-24 12:54:37 Re: very long update gin index troubles back?