Re: Preserving datatypes in dblink.

On Fri, 23 Aug 2002, Joe Conway wrote:

> 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

Yeah, I did try the same earlier but it says
ERROR: Cannot cast type 'text' to 'text[]'

So how do we cast text to text[](_text)? Is there any alternate way?
Awaiting for your valuable suggestion again, please.

regards,
bhuvaneswaran