pg.ddx.io pgsql-php@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to Create a table in another database
7+ messages / 5 participants
[nested] [flat]
* How to Create a table in another database
@ 2015-11-12 10:26 Archana K N <archanakknn@gmail.com>
0 siblings, 2 replies; 7+ messages in thread
From: Archana K N @ 2015-11-12 10:26 UTC (permalink / raw)
To: pgsql-php
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
--------------------
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How to Create a table in another database
@ 2015-11-12 18:56 Guillaume Lelarge <guillaume@lelarge.info>
parent: Archana K N <archanakknn@gmail.com>
1 sibling, 1 reply; 7+ messages in thread
From: Guillaume Lelarge @ 2015-11-12 18:56 UTC (permalink / raw)
To: Archana K N <archanakknn@gmail.com>; +Cc: pgsql-php
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How to Create a table in another database
@ 2015-11-12 20:21 itb348@gmail.com
parent: Guillaume Lelarge <guillaume@lelarge.info>
0 siblings, 2 replies; 7+ messages in thread
From: itb348@gmail.com @ 2015-11-12 20:21 UTC (permalink / raw)
To: pgsql-php
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>>:
>
> 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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How to Create a table in another database
@ 2015-11-13 05:35 Archana K N <archanakknn@gmail.com>
parent: itb348@gmail.com
1 sibling, 1 reply; 7+ messages in thread
From: Archana K N @ 2015-11-13 05:35 UTC (permalink / raw)
To: pgsql-php
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
--------------------
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How to Create a table in another database
@ 2015-11-13 11:54 Raymond O'Donnell <rod@iol.ie>
parent: Archana K N <archanakknn@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Raymond O'Donnell @ 2015-11-13 11:54 UTC (permalink / raw)
To: Archana K N <archanakknn@gmail.com>; pgsql-php
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
--
Sent via pgsql-php mailing list (pgsql-php@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-php
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How to Create a table in another database
@ 2015-11-13 12:16 Matthias Ritzkowski <matthiar@gmail.com>
parent: Archana K N <archanakknn@gmail.com>
1 sibling, 0 replies; 7+ messages in thread
From: Matthias Ritzkowski @ 2015-11-13 12:16 UTC (permalink / raw)
To: Archana K N <archanakknn@gmail.com>; +Cc: pgsql-php
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
> --------------------
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How to Create a table in another database
@ 2015-11-13 13:43 Raymond O'Donnell <rod@iol.ie>
parent: itb348@gmail.com
1 sibling, 0 replies; 7+ messages in thread
From: Raymond O'Donnell @ 2015-11-13 13:43 UTC (permalink / raw)
To: itb348@gmail.com; pgsql-php
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
--
Sent via pgsql-php mailing list (pgsql-php@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-php
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2015-11-13 13:43 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-11-12 10:26 How to Create a table in another database Archana K N <archanakknn@gmail.com>
2015-11-12 18:56 ` Guillaume Lelarge <guillaume@lelarge.info>
2015-11-12 20:21 ` itb348@gmail.com
2015-11-13 05:35 ` Archana K N <archanakknn@gmail.com>
2015-11-13 11:54 ` Raymond O'Donnell <rod@iol.ie>
2015-11-13 13:43 ` Raymond O'Donnell <rod@iol.ie>
2015-11-13 12:16 ` Matthias Ritzkowski <matthiar@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox