BUG #5813: Cross Database Access in the same server using DBlink

From: "Peevee" <peevee12(at)yahoo(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #5813: Cross Database Access in the same server using DBlink
Date: 2011-01-05 11:20:46
Message-ID: 201101051120.p05BKkgQ046232@wwwmaster.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs


The following bug has been logged online:

Bug reference: 5813
Logged by: Peevee
Email address: peevee12(at)yahoo(dot)com
PostgreSQL version: 9.0.2
Operating system: Windows 7
Description: Cross Database Access in the same server using DBlink
Details:

I have created a table named "TestTableA" in a Database named "DatabaseA"
and created again another table named "TestTableB" in "DatabaseB".

CREATE TABLE "TestTableA"
(
"Name" character varying(50)[],
"TableId" serial NOT NULL,
CONSTRAINT "pk_tableID" PRIMARY KEY ("TableId")
)
WITH (
OIDS=FALSE
);
ALTER TABLE "TestTableA" OWNER TO postgres;

CREATE TABLE "TestTableB"
(
"TableId" serial NOT NULL,
"Name" character varying(50)[],
CONSTRAINT "pk_tableID" PRIMARY KEY ("TableId")
)
WITH (
OIDS=FALSE
);
ALTER TABLE "TestTableB" OWNER TO postgres;

Now I'm trying to access "TableB" which is located in "DatabaseB" from my
function located in "DatabaseA".

create or replace function TestFunction(pInput character varying) RETURNS
VOID as
$$
DECLARE

rec record;
BEGIN

SELECT * from dblink('dbname=DMATempLog port=5432 user=postgres
password=somepassword','Select TableName from TestTableB LIMIT 1' )as
rec(tablename character varying(50));
END;
$$ language plpgsql;

and this generates an error:

ERROR: relation "testtableb" does not exist
CONTEXT: Error occurred on dblink connection named "unnamed": could not
execute query.
SQL statement "SELECT * from dblink('dbname=DMATempLog port=5433
user=postgres password=Pv12062010igen','Select TableName from testtableb
LIMIT 1' )as rec(tablename character varying(50))"
PL/pgSQL function "testfunction" line 12 at SQL statement

********** Error **********

ERROR: relation "testtableb" does not exist
SQL state: 42P01
Context: Error occurred on dblink connection named "unnamed": could not
execute query.
SQL statement "SELECT * from dblink('dbname=DMATempLog port=5433
user=postgres password=Pv12062010igen','Select TableName from testtableb
LIMIT 1' )as rec(tablename character varying(50))"
PL/pgSQL function "testfunction" line 12 at SQL statement

I'm solving this problem for almost 1 week..could you please help me..i read
a lot about dblink and try all their suggestions but none of them works.Am i
missing something here?

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Antje Petersen 2011-01-05 13:04:24 BUG #5814: documentation bug
Previous Message harshad 2011-01-05 11:17:43 certification courses for postgreSQL......