Postgresql 9.6, Calling a remote function with data wrapper throws various errors

Question

I've created a function in a database that inserts records into a table. This function returns VOID and takes a VARIADIC text array as an input param. When I run the function from the database locally, it works fine, as expected.

But when I try to run from a different database, using a foreign data wrapper it will not work, throws different errors depending on the method I use.

Here's how I make one kind of call:

SELECT dblink('pg_log', 
'SELECT public.insert_log(''usage'', ''txn'', ''dimensions'', ''test'', null, 
''pgwrapper'', ''temp_var'', null,  null, null, ''Start'', null, 
                     null, null, null);');

That one throws this error:

function returning record called in context that cannot accept type record

When I replace Select dblink with PERFORM dblink, I get this error:

syntax error at or near "PERFORM"

And when I try, SELECT dblink_exec:

I get this error:

statement returning results not allowed

Again, the function works as I have called it locally to test it and it does what it should.

I checked the connection with this and it return OK:

SELECT dblink_connect('pg_log');

Anyone have any ideas why this is failing and suggestions on fixing?

Thanks!

I haven't done this before but maybe try SELECT * FROM dblink(.....). It looks like dblink returns a record so you need to select from that. — Gregory Arenius
– Gregory Arenius, Commented Apr 26, 2018 at 21:22
Thanks! Just tried that it returns this error: a column definition list is required for functions returning "record". Which doesn't make sense to me because the function being called returns VOID. — EricBlair1984
– EricBlair1984, Commented Apr 26, 2018 at 21:26
Your insert_log function returns void, but dblink returns a record. I think in this case that might be a record with no columns? Maybe try SELECT * FROM dblink(...) AS t1(); Or maybe SELECT * FROM dblink(...) as t1(c1 VOID); — Gregory Arenius
– Gregory Arenius, Commented Apr 26, 2018 at 21:31
VOID type doesn't work, but Character Varying type does. I accepted your answer below, with my tweak. Thanks! — EricBlair1984
– EricBlair1984, Commented Apr 26, 2018 at 21:33

Gregory Arenius · Accepted Answer · 2018-04-26 21:36:01Z

1

It looks like you need to try SELECT * FROM dblink(...) AS t1(column_name type) instead of SELECT dblink(...).

From the PostgresSQL Documenation:

The function returns the row(s) produced by the query. Since dblink can be used with any query, it is declared to return record, rather than specifying any particular set of columns. This means that you must specify the expected set of columns in the calling query — otherwise PostgreSQL would not know what to expect. Here is an example:

SELECT *
FROM dblink('dbname=mydb options=-csearch_path=',
            'select proname, prosrc from pg_proc')
  AS t1(proname name, prosrc text)
WHERE proname LIKE 'bytea%';

edited Apr 26, 2018 at 21:36

answered Apr 26, 2018 at 21:27

Gregory Arenius

3,3046 gold badges33 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

EricBlair1984 Over a year ago

Yes, I did that and it works now, like this Select f.a from dblink(...): as f(a character varying); Works! Thanks for the help.

Collectives™ on Stack Overflow

Postgresql 9.6, Calling a remote function with data wrapper throws various errors

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related