Re: Preserving datatypes in dblink.

Bhuvan A wrote:
> 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;
>

That's because dblink_tok is declared to return text (it *has* to be
declared to return something, so text is the best choice). Explicitly
cast the columns in you view to whatever datatype is correct. I.e.
(untested)

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)::timestamp with time zone AS ctime,
  dblink_tok(t.ptr, 3)::timestamp with time zone 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;

Joe