Обсуждение: Preserving datatypes in dblink.

Поиск
Список
Период
Сортировка

Preserving datatypes in dblink.

От
Bhuvan A
Дата:
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;

But my requirement is to preserve the datatype of all the fields in dblink
view and to insert the records from dblink view to a similar source table
in database db2.

How can i do this?

Really i have struck in the middle of my production work. Is it possible
to preserve datatypes in dblink views? I request you to treat this as
urgent and throw some light on this or some alternates.

regards,
bhuvaneswaran

Re: Preserving datatypes in dblink.

От
Joe Conway
Дата:
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

Re: Preserving datatypes in dblink.

От
Bhuvan A
Дата:
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
>

Re: Preserving datatypes in dblink.

От
Joe Conway
Дата:
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

Re: Preserving datatypes in dblink.

От
Bhuvan A
Дата:
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

Re: Preserving datatypes in dblink.

От
Joe Conway
Дата:
Bhuvan A wrote:
> 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.

Hmmm. I see what you mean. I'm afraid there may be no way to return an
array via dblink currently :(

I am just starting the next upgrade to dblink to support table functions
in PostgreSQL 7.3 (which starts beta testing around September 1). Table
functions should make dblink work much more naturally, something like
(not yet working, but hopefully should be by next weekend on 7.3devel):

select
  d.f1, d.f2
from
  dblink('dbname=mydb', 'select f1, f2 from remotetbl')
  as d(f1 int, f2 text);

This would return f1 as an int, and f2 as text without the need to cast
them in the target list. I would think that arrays would return fine
this way, but I'll check as I'm working on it.

Joe

Re: Preserving datatypes in dblink.

От
Joe Conway
Дата:
Bhuvan A wrote:
> 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.

Just as an FYI -- as of a few minutes ago, I've gotten the following to
work:

test3=# create table foo(f1 text[]);
CREATE TABLE
test3=# insert into foo values('{a,b,c,d}');
INSERT 250715 1
test3=# insert into foo values('{e,f,g,h}');
INSERT 250716 1
test3=# \c test
You are now connected to database test.
test=# select * from dblink('dbname=test3','select f1 from foo') as t(f1
text[]);
     f1
-----------
  {a,b,c,d}
  {e,f,g,h}
(2 rows)

test=# select f1[1] from dblink('dbname=test3','select f1 from foo') as
t(f1 text[]);
  f1
----
  a
  e
(2 rows)

So, although I can't help you in 7.2.x, it looks like dblink will do
what you need in PostgreSQL 7.3 (which starts beta in a few days).

HTH,

Joe