Skip site navigation (1) Skip section navigation (2)

Preserving datatypes in dblink.

From: Bhuvan A <bhuvansql(at)linuxfreemail(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: Preserving datatypes in dblink.
Date: 2002-08-23 05:54:04
Message-ID: Pine.LNX.4.44.0208231059400.1946-100000@Bhuvan.bksys.co.in (view raw or flat)
Thread:
Lists: pgsql-admin
Hi,

I am using postgresql 7.2.1.

I am using dblink function in order to execute remote queries. I did this
by creating a view (as suggested by README.dblink). Here i found a strange
thing that the datatype of all the fields of this view is set to text,
irrespect of the source datatype.

Here is an example.

# \c db1
# \d my_table
                 Table "my_table"
 Column |           Type           |   Modifiers
--------+--------------------------+---------------
 key    | text                     |
 value  | text                     |
 ctime  | timestamp with time zone | default now()
 mtime  | timestamp with time zone |

# \c db2 
# CREATE VIEW dbl_my_view AS SELECT dblink_tok(t.ptr, 0) AS key,
dblink_tok(t.ptr, 1) AS value, dblink_tok(t.ptr, 2) AS ctime,
dblink_tok(t.ptr, 3) AS mtime FROM (SELECT dblink('hostaddr=192.168.1.15
port=5432 dbname=db1 user=my_user password=my_pass', 'select key, value,
ctime, mtime from my_table') AS ptr) t;
CREATE
# \d dbl_my_view
       View "dbl_my_view"
 Column | Type | Modifiers
--------+------+-----------
 key    | text |
 value  | text |
 ctime  | text |
 mtime  | text | 
View definition: SELECT dblink_tok(t.ptr, 0) AS "key", dblink_tok(t.ptr,
1) AS value, dblink_tok(t.ptr, 2) AS ctime, dblink_tok(t.ptr, 3) AS mtime
FROM (SELECT dblink('hostaddr=192.168.1.15 port=5432 dbname=db1
user=my_user password=my_pass', 'select key, value, ctime, mtime from
my_table'::text) AS ptr) t;

But my requirement is to preserve the datatype of all the fields in dblink
view and to insert the records from dblink view to a similar source table
in database db2. 

How can i do this? 

Really i have struck in the middle of my production work. Is it possible
to preserve datatypes in dblink views? I request you to treat this as
urgent and throw some light on this or some alternates.

regards, 
bhuvaneswaran


Responses

pgsql-admin by date

Next:From: Joe ConwayDate: 2002-08-23 06:07:03
Subject: Re: Preserving datatypes in dblink.
Previous:From: Theodore A. JencksDate: 2002-08-23 05:03:46
Subject: Createlang problem.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group