Re: How to Create a table in another database

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

Hey,

2015-11-12 11:26 GMT+01:00 Archana K N <archanakknn(at)gmail(dot)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

On 12.11.2015 19:56, Guillaume Lelarge wrote:
> Hey,
>
> 2015-11-12 11:26 GMT+01:00 Archana K N <archanakknn(at)gmail(dot)com
> <mailto:archanakknn(at)gmail(dot)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.

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(at)gmail(dot)com> wrote:

> On 12.11.2015 19:56, Guillaume Lelarge wrote:
>
> Hey,
>
> 2015-11-12 11:26 GMT+01:00 Archana K N <archanakknn(at)gmail(dot)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
--------------------

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(at)iol(dot)ie

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(at)gmail(dot)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
> --------------------
>

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