Re: multi-database - Mailing list pgsql-general

Shridhar Daithankar wrote:
> On 27 Sep 2002 at 10:34, mourad_dhambri wrote:
>>what I want is to delete data from another database db2 and to insert data from my database db1 to db2.
>>I don't think that dblink can do so. Is there another way ?
>
> Why not? Just create a view for table(s) in db1 in db2. And do following on db2
> delete from table1;insert into table1 select * from remoteview;
> dblink documentation has example as how to create such a remote view..
>

The original version of dblink (which is distributed with 7.2.x) does not
support anything other than SELECT statements due to the fact that it wraps
your statement in a CURSOR.

The version of dblink in 7.3 (in beta now) has a new function, dblink_exec,
which is specifically intended for INSERT/UPDATE/DELETE. If you can, please
give the beta a try.

I have a patch that allows dblink in 7.2 to execute INSERT/UPDATE/DELETE
statements. I'll send it to you off-list if you want (let me know), but it
would be better if you can wait for 7.3 to be released and use it.

Joe

p.s. In case anyone is interested, here's a brief synopsis of the new 7.3
dblink functions from the README:

      connection
      ------------
      dblink_connect(text) RETURNS text
        - opens a connection that will persist for duration of current
          backend or until it is disconnected
      dblink_disconnect() RETURNS text
        - disconnects a persistent connection

      cursor
      ------------
      dblink_open(text,text) RETURNS text
        - opens a cursor using connection already opened with dblink_connect()
          that will persist for duration of current backend or until it is
          closed
      dblink_fetch(text, int) RETURNS setof record
        - fetches data from an already opened cursor
      dblink_close(text) RETURNS text
        - closes a cursor

      query
      ------------
      dblink(text,text) RETURNS setof record
        - returns a set of results from remote SELECT query
          (Note: comment out in dblink.sql to use deprecated version)
      dblink(text) RETURNS setof record
        - returns a set of results from remote SELECT query, using connection
          already opened with dblink_connect()

      execute
      ------------
      dblink_exec(text, text) RETURNS text
        - executes an INSERT/UPDATE/DELETE query remotely
      dblink_exec(text) RETURNS text
        - executes an INSERT/UPDATE/DELETE query remotely, using connection
          already opened with dblink_connect()

      misc
      ------------
      dblink_current_query() RETURNS text
        - returns the current query string
      dblink_get_pkey(text) RETURNS setof text
        - returns the field names of a relation's primary key fields
      dblink_build_sql_insert(text,int2vector,int2,_text,_text) RETURNS text
        - builds an insert statement using a local tuple, replacing the
          selection key field values with alternate supplied values
      dblink_build_sql_delete(text,int2vector,int2,_text) RETURNS text
        - builds a delete statement using supplied values for selection
          key field values
      dblink_build_sql_update(text,int2vector,int2,_text,_text) RETURNS text
        - builds an update statement using a local tuple, replacing the
          selection key field values with alternate supplied values

   Not installed by default
      deprecated
      ------------
      dblink(text,text) RETURNS setof int
        - *DEPRECATED* returns a resource id for results from remote query
          (Note: must uncomment in dblink.sql to use)
      dblink_tok(int,int) RETURNS text
        - *DEPRECATED* extracts and returns individual field results; used
          only in conjunction with the *DEPRECATED* form of dblink
          (Note: must uncomment in dblink.sql to use)
      dblink_last_oid(int) RETURNS oid
        - *DEPRECATED* returns the last inserted oid