Re: Preserving datatypes in dblink.

Bhuvan A wrote:
> On Thu, 22 Aug 2002, Joe Conway wrote:
>
>
>>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
>>
>
>
> Thankx for your suggestion. This casting method is applicable for all
> datatypes like int, decimal, date, timestamp. But how about arrays like
> text[]? How do we cast from text to text[]? Its where i stick again.
> Expecting your much valuable suggestion.
>

I think it would be:
   dblink_tok(t.ptr, 2)::_text

Array types are the base type name with an underscore in front.

Joe