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!
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%';