Re: How to Create a table in another database

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