Re: How to Create a table in another database

[Archana K N <archanakknn@xxxxxxxxx>]

  Hello,

      Here is my whole function...

*************************************************************************************************************

CREATE OR REPLACE FUNCTION retriev() RETURN void  AS

$tmp$

DECLARE 

         colnam text[];

         j text;

         sum1 numeric;

         totsum numeric;

         datfrm text;  

         datto  text;

         

         

BEGIN

    datfrm := quote_literal('2000-11-16');

    datto  := quote_literal('2015-11-02');

    totsum :=0;

 

    colnam := array(select distinct table_schema::text from information_schema.tables where table_schema like '%ab%');

    

  

     EXECUTE 'CREATE TABLE db2.public.temp(totalsum  numeric(100,20))';

     FOR  j in array_lower(colnam,1).. array_upper(colnam,1)

      LOOP

        EXECUTE ' SELECT coalesce(sum(db1_col),0)date between '||datfrm||' AND '||datto into sum1;

        totsum := totsum + sum1; 

   

        INSERT INTO db2.public.temp(totalsum) VALUES(totsum);                         

      END LOOP;

END

$tmp$

LANGUAGE 'plpgsql' VOLATILE;

--Calling the function

select retriev()

***************************************************************************************************************************************

 Here the underlined queries are for db2.  I need to create function in the second database since first database contain data that is not for public access. So from second database I call the db1.retriev and it uses necessary data from db1 and result is saved as a table in second database. Actual function has more parameters and result may contain many rows , for that I want to create table in db2.

Regards

Archana 

On Fri, Nov 13, 2015 at 1:51 AM, <itb348@xxxxxxxxx> wrote:

On 12.11.2015 19:56, Guillaume Lelarge wrote:

Hey,

2015-11-12 11:26 GMT+01:00 Archana K N <archanakknn@xxxxxxxxx>:

Hello,

     I created a postgres function in db1, from which I need to create a table in another database say db2. I tried to use 'dblink' but it is giving the error:-

   ERROR:  function dblink_connect(unknown, unknown) does not exist

   LINE 1: select dblink_connect('dbname=mydb port=5432 host=localhost ...

               ^

   HINT:  No function matches the given name and argument types. You might need       to add explicit type casts.

     Have been stuck on this for 2 days. Dont know what to do. 

     

     I am using Windows 7 OS(32-bit) and due to certain security purpose am using         Postgres 9.0(cant update it to newer version). 

      Please help me. 

Have you installed dblink on the db1 database? If yes, can you give us the complete query that begins with "select dblink_connect..." ?

to me it looks like Archana is mixing PHP and SQL.  "more details" certainly is the right reaction here.

/Str.

--

-------------------
regards
archana
--------------------