The following bug has been logged online:
Bug reference: 5813
Logged by: Peevee
Email address: peevee12@yahoo.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?
