pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
How 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>
  2015-11-12 18:56 ` Re: How to Create a table in another database Guillaume Lelarge <guillaume@lelarge.info>
  2015-11-13 12:16 ` Re: How to Create a table in another database Matthias Ritzkowski <matthiar@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 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   ` Re: How to Create a table in another database itb348@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 10:26 How to Create a table in another database Archana K N <archanakknn@gmail.com>
  2015-11-12 18:56 ` Re: How to Create a table in another database Guillaume Lelarge <guillaume@lelarge.info>
@ 2015-11-12 20:21   ` itb348@gmail.com
  2015-11-13 05:35     ` Re: How to Create a table in another database Archana K N <archanakknn@gmail.com>
  2015-11-13 13:43     ` Re: How to Create a table in another database Raymond O'Donnell <rod@iol.ie>
  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-12 10:26 How to Create a table in another database Archana K N <archanakknn@gmail.com>
  2015-11-12 18:56 ` Re: How to Create a table in another database Guillaume Lelarge <guillaume@lelarge.info>
  2015-11-12 20:21   ` Re: How to Create a table in another database itb348@gmail.com
@ 2015-11-13 05:35     ` Archana K N <archanakknn@gmail.com>
  2015-11-13 11:54       ` Re: How to Create a table in another database Raymond O'Donnell <rod@iol.ie>
  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-12 10:26 How to Create a table in another database Archana K N <archanakknn@gmail.com>
  2015-11-12 18:56 ` Re: How to Create a table in another database Guillaume Lelarge <guillaume@lelarge.info>
  2015-11-12 20:21   ` Re: How to Create a table in another database itb348@gmail.com
  2015-11-13 05:35     ` Re: How to Create a table in another database Archana K N <archanakknn@gmail.com>
@ 2015-11-13 11:54       ` Raymond O'Donnell <rod@iol.ie>
  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-12 10:26 How to Create a table in another database Archana K N <archanakknn@gmail.com>
  2015-11-12 18:56 ` Re: How to Create a table in another database Guillaume Lelarge <guillaume@lelarge.info>
  2015-11-12 20:21   ` Re: How to Create a table in another database itb348@gmail.com
@ 2015-11-13 13:43     ` Raymond O'Donnell <rod@iol.ie>
  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

* Re: How to Create a table in another database
  2015-11-12 10:26 How to Create a table in another database Archana K N <archanakknn@gmail.com>
@ 2015-11-13 12:16 ` Matthias Ritzkowski <matthiar@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


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