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
