1

I want to use dblink in PL/pgSQL stored procedure in such way:

PERFORM dblink_exec('myconn', 'BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE');
PERFORM dblink_exec('myconn', 'SELECT another_stored_procedure()');
PERFORM dblink_exec('myconn', 'COMMIT');

but I got an error in runtime:

ERROR:  statement returning results not allowed
CONTEXT:  SQL statement "SELECT dblink_exec('myconn', 'select another_stored_procedure()')"

so execution fails, although I tried to get the desired result in different ways.

UPDATE 1:

I know that stored procedures in postgresql are transactional. I'm using dblink for the autonomous transactions functionality to use it on the same server.

The matter is that the default level of transactions on my server is "read commited" but sometimes I need to start transactions with another level, e.g. "serializable".

So I need to execute stored procedure in autonomous transaction with explicit transaction level specifying.

And as far as I know dblink allows that, but I failed to find any useful info about dblink or dblink_exec functions which are suitable for my situation.

FrozenHeart
  • 19,844
  • 33
  • 126
  • 242

1 Answers1

7

I assume, you have connected with another PostgreSQL server at the other end.

You need to call the dblink() function to execute statements, which has result(s), and not dblink_exec(). (Even if your function on the other end has returns void -- in that case, you could get a single NULL from calling that function in a SELECT.)

Also, you might not need transaction management:

In short, you need to execute:

-- PERFORM dblink_exec('myconn', 'BEGIN ...');
--    if you need explicit transaction management
PERFORM * FROM dblink('myconn', 'SELECT another_stored_procedure()') alias(col text);
-- PERFORM dblink_exec('myconn', 'COMMIT');
Community
  • 1
  • 1
pozs
  • 34,608
  • 5
  • 57
  • 63
  • 1
    @FrozenHeart that doesn't matter (that's why I wrote you might not need transaction management). As I stated, you should use `dblink()` for the `SELECT` statement. Have you tried yet? – pozs Feb 03 '15 at 14:50
  • 1
    Thanks for aiming. Your variant failed with: `ERROR: statement returning results not allowed` but this works for me (my another_stored_procedure() returns void): `PERFORM * FROM dblink('myconn', 'SELECT another_stored_procedure()') as t1(test text);` – FrozenHeart Feb 03 '15 at 15:04
  • @FrozenHeart yeah, I forgot `returns setof record` (like `dblink()` returns) always need an alias, where you specify column alias/types. – pozs Feb 03 '15 at 15:28