Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-admin by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group