Обсуждение: Preserving datatypes in dblink.
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
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
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 >
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
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
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
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