Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

[PHP] How to Create a table in another database

2 views
Skip to first unread message

Archana K N

unread,
Nov 12, 2015, 5:27:11 AM11/12/15
to
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. 

--
-------------------
regards
Archana K N
--------------------

Guillaume Lelarge

unread,
Nov 12, 2015, 1:56:54 PM11/12/15
to
Hey,

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

Thanks.


--

itb...@gmail.com

unread,
Nov 12, 2015, 3:30:11 PM11/12/15
to
to me it looks like Archana is mixing PHP and SQL.  "more details" certainly is the right reaction here.

/Str.

Archana K N

unread,
Nov 13, 2015, 12:36:31 AM11/13/15
to
  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 
--
-------------------
regards
archana
--------------------

Raymond O'Donnell

unread,
Nov 13, 2015, 6:54:58 AM11/13/15
to
On 13/11/2015 05:35, Archana K N wrote:

> 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/*.

Have you considered using two separate schemas within the same database?
You can set permissions on them so that one is for public access and the
other isn't; it would be far easier than messing with two databases.

Ray.


--
Raymond O'Donnell :: Galway :: Ireland
r...@iol.ie


--
Sent via pgsql-php mailing list (pgsq...@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-php

Matthias Ritzkowski

unread,
Nov 13, 2015, 7:16:55 AM11/13/15
to

Have you installed AND activated dblink?

You can check with \dx from a pgsql prompt ...

This is a good guide:
http://michael.otacoo.com/postgresql-2/first-steps-with-dblink-on-postgres/

regards
Matthias Ritzkowski

On Nov 12, 2015 05:27, "Archana K N" <archa...@gmail.com> wrote:
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. 

Raymond O'Donnell

unread,
Nov 13, 2015, 8:44:45 AM11/13/15
to
On 12/11/2015 20:21, itb...@gmail.com wrote:
> On 12.11.2015 19:56, Guillaume Lelarge wrote:
>> Hey,
>>
>> 2015-11-12 11:26 GMT+01:00 Archana K N <archa...@gmail.com
>> <mailto:archa...@gmail.com>>:
>>


By the way, you're posting to the pgsql-php mailing list, but your
question seems to have nothing to do with PHP, so you'd be better off
posting to pgsql-general - it has a much bigger readership.
0 new messages