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?
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...... |