Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2011
    Posts
    2

    Unanswered: Cross Database Reference in the same server

    I have created a table named "TestTableA" in a Database named "DatabaseA"
    and created again another table named "TestTableB" in "DatabaseB".
    Code:
    CREATE DATABASE "DatabaseA"
      WITH OWNER = postgres
           ENCODING = 'UTF8'
           TABLESPACE = pg_default
           LC_COLLATE = 'English_United States.1252'
           LC_CTYPE = 'English_United States.1252'
           CONNECTION LIMIT = -1;
    GRANT ALL ON DATABASE "DatabaseA" TO public;
    GRANT ALL ON DATABASE "DatabaseA" TO postgres;
    
    ALTER DEFAULT PRIVILEGES 
        GRANT INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER ON TABLES
        TO public;
    
    ALTER DEFAULT PRIVILEGES 
        GRANT INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER ON TABLES
        TO postgres;
    
    -- Table: "TestTableA"
    
    -- DROP TABLE "TestTableA";
    
    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;
    GRANT ALL ON TABLE "TestTableA" TO public;
    GRANT ALL ON TABLE "TestTableA" TO postgres;
    
    -- Database: "DatabaseB"
    
    -- DROP DATABASE "DatabaseB";
    
    CREATE DATABASE "DatabaseB"
      WITH OWNER = postgres
           ENCODING = 'UTF8'
           TABLESPACE = pg_default
           LC_COLLATE = 'English_United States.1252'
           LC_CTYPE = 'English_United States.1252'
           CONNECTION LIMIT = -1;
    GRANT ALL ON DATABASE "DatabaseB" TO public;
    GRANT ALL ON DATABASE "DatabaseB" TO postgres;
    
    ALTER DEFAULT PRIVILEGES 
        GRANT INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER ON TABLES
        TO public;
    
    ALTER DEFAULT PRIVILEGES 
        GRANT INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER ON TABLES
        TO postgres;
    -- Table: "TestTableB"
    
    -- DROP TABLE "TestTableB";
    
    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;
    GRANT ALL ON TABLE "TestTableB" TO public;
    GRANT ALL ON TABLE "TestTableB" TO postgres;


    Now I'm trying to access "TableB" which is located in "DatabaseB" from my
    function located in "DatabaseA".This function actually do nothing. It simply selects a record.The parameter here also has no special role.

    Code:
    -- Function: testfunction(character varying)
    
    -- DROP FUNCTION testfunction(character varying);
    
    CREATE OR REPLACE FUNCTION testfunction(pinput character varying)
      RETURNS void AS
    $BODY$
    DECLARE 
    	myconnstring character varying;
    	rec record;
    BEGIN
    		
    	SELECT * from dblink('dbname=DatabaseB port=5432 user=postgres password=password','Select TableName from testtableb LIMIT 1' )as rec(tablename character varying(50));
    	--SELECT DMALog-Temp.Public.
    END;
    $BODY$
      LANGUAGE plpgsql VOLATILE
      COST 100;
    ALTER FUNCTION testfunction(character varying) OWNER TO postgres;
    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=DatabaseB host=localhost port=5432 user=postgres password=password','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=DatabaseB host=localhost port=5432 user=postgres password=password','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? I can connect to DatabaseB using this code
    Code:
    SELECT * from dblink_connect('dbname=DatabaseB port=5432 user=postgres password=password');
    but if i use dblink() it seems that I can 't connect or I can connect but sometimes an error will appear saying that cross database reference not allowed.This error will appear if I modify my code a little bit.Any help please?

    I Am using PostGresSQL 9.0.2 in Windows 7 64 Bit

  2. #2
    Join Date
    Nov 2006
    Posts
    82
    Your table name is TestTableB, and because in create script you put this string into " " the name is case-sensitive.
    So the query
    select .... from testtableb ...
    should be changed to
    Code:
    select ...... from "TestTableB" ...

  3. #3
    Join Date
    Jan 2011
    Posts
    1

    Tray this

    Try this

    select
    SELECT * FROM dblink('dbname=postgres port=5432 user=postgres password=postgres','SELECT name, password FROM accounts')
    AS (n varchar(21), p varchar(21));

    insert
    SELECT dblink_exec('dbname=postgres port=5432 user=postgres password=postgres','insert into accounts (name,password)values (''dd'',''pwd'');');

    Its worked for me.

    Mangwantong

  4. #4
    Join Date
    Jan 2011
    Posts
    2
    @rski- the double qoutation marks "" really works..its the only syntax that I lack..thanks...now my app is ready for testing...thanks for the brilliant observation

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •