Re: Preserving datatypes in dblink.

From: Bhuvan A <bhuvansql(at)linuxfreemail(dot)com>
To: Joe Conway <mail(at)joeconway(dot)com>
Cc: Bhuvan A <bhuvansql(at)linuxfreemail(dot)com>, <pgsql-admin(at)postgresql(dot)org>
Subject: Re: Preserving datatypes in dblink.
Date: 2002-08-23 09:55:56
Message-ID: Pine.LNX.4.44.0208231520110.1584-100000@Bhuvan.bksys.co.in
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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.

TIA

regards,
bhuvaneswaran

>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

In response to

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Alex Paulusberger 2002-08-23 13:11:21 64 Bit build for Sparc
Previous Message Guillaume MARTIN 2002-08-23 09:35:36 Re: Vacuum failed !