Thread: How to Create a table in another database

How to Create a table in another database

From

Archana K N

Date:

12 November 2015, 13:27:01

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

Re: How to Create a table in another database

From

Guillaume Lelarge

Date:

12 November 2015, 21:56:50

Hey,

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

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

Thanks.

Guillaume.

http://blog.guillaume.lelarge.info

http://www.dalibo.com

Re: How to Create a table in another database

From

itb348@gmail.com

Date:

12 November 2015, 23:30:07

On 12.11.2015 19:56, Guillaume Lelarge wrote:

Hey,

2015-11-12 11:26 GMT+01:00 Archana K N <archanakknn@gmail.com>:
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.

Re: How to Create a table in another database

From

Archana K N

Date:

13 November 2015, 08:35:49

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@gmail.com> wrote:

On 12.11.2015 19:56, Guillaume Lelarge wrote:
Hey,

2015-11-12 11:26 GMT+01:00 Archana K N <archanakknn@gmail.com>:
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
--------------------

Re: How to Create a table in another database

From

Raymond O'Donnell

Date:

13 November 2015, 14:54:54

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
rod@iol.ie

Re: How to Create a table in another database

From

Matthias Ritzkowski

Date:

13 November 2015, 15:16:14

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" <archanakknn@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.

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

Re: How to Create a table in another database

From

Raymond O'Donnell

Date:

13 November 2015, 16:44:05

On 12/11/2015 20:21, itb348@gmail.com wrote:
> On 12.11.2015 19:56, Guillaume Lelarge wrote:
>> Hey,
>>
>> 2015-11-12 11:26 GMT+01:00 Archana K N <archanakknn@gmail.com
>> <mailto:archanakknn@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.

Ray.

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