PostgreSQL dblink with named connections
dblink
doesn't seem to work when I use a named connection to a remote server or an unnamed connection and disconnect. It works fine if I use an unnamed connection with a connection string in dblink (). It seems to be connected fine, but my connection is not available when I try to use it. Any ideas on how to get this to work with named connections?
Unnamed with connstr Works Fine:
SELECT testtable.*
FROM dblink('dbname=testdb port=5432 host=192.168.1.1 user=usr password=pw'
,'SELECT * FROM testtable')
AS testtable(testtable_id integer, testtable_name text);
Returns: two columns as expected.
Named doesn't work:
Connect:
SELECT dblink_connect('myconn'
,'dbname=testdb port=5432 host=192.168.1.1 user=usr password=pw');
Returns: "OK"
Query:
SELECT testtable.* FROM dblink('myconn', 'SELECT * FROM testtable')
AS testtable(testtable_id integer, testtable_name text);
Returns:
ERROR: could not establish connection
DETAIL: missing "=" after "myconn" in connection info string
********** Error **********
ERROR: could not establish connection
SQL state: 08001
Detail: missing "=" after "myconn" in connection info string
Disconnect:
SELECT dblink_disconnect('myconn');
Returns:
ERROR: connection "myconn" not available
********** Error **********
ERROR: connection "myconn" not available
SQL state: 08003
Unnamed with _connect and _disconnect doesn't work:
Connect:
SELECT dblink_connect('dbname=testdb port=5432 host=192.168.1.1
user=usr password=pw');
Returns: "OK"
Query:
SELECT testtable.* FROM dblink('SELECT * FROM testtable')
AS testtable(testtable_id integer, testtable_name text);
Returns:
ERROR: connection not available
********** Error **********
ERROR: connection not available
SQL state: 08003
Disconnect:
SELECT dblink_disconnect();
Returns:
ERROR: connection not available
********** Error **********
ERROR: connection not available
SQL state: 08003
source to share
I have a working setup with unnamed connections.
What you call " No Name " in your question actually has a name parameter in it. You are mixing the two. Try this without 'myconn'
:
SELECT *
FROM dblink('SELECT * FROM testtable'
) AS testtable (testtable_id integer, testtable_name text);
And remember that establishing a connection and using it must be done in the same session.
But I honestly can't find what happened to your connection name . I've run some tests and everything looks correct. I have tested PostgreSQL 9.1.
The error message means that dblink is expecting a connstr
. This only happens if the first parameter does not match the existing one connname
. Long story short: no connection 'myconn'
found, which makes me suspect that you are not calling dblink()
in the same session as dblink_connect()
.
source to share