[GENERAL] arrays of rows and dblink
Hi, we have the ROW type and we have arrays. We also can create arrays of rows like: select array_agg(r) from (values (1::int, 'today'::timestamp, 'a'::text), (2, 'yesterday', 'b')) r(a,b,c); array_agg --- {"(1,\"2014-04-30 00:00:00\",a)","(2,\"2014-04-29 00:00:00\",b)"} Now I want to execute that query via dblink on a remote server. How do I specify the result type? select tb.* from dblink( 'dbname=postgres', $$ select array_agg(r) from (values (1::int, 'today'::timestamp, 'a'::text), $$) tb( WHAT DO I PUT HERE? ) Thanks, Torsten -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] arrays of rows and dblink
Torsten Förtsch wrote > Hi, > > we have the ROW type and we have arrays. We also can create arrays of > rows like: > > select array_agg(r) > from (values (1::int, 'today'::timestamp, 'a'::text), >(2, 'yesterday', 'b')) r(a,b,c); > array_agg > --- > {"(1,\"2014-04-30 00:00:00\",a)","(2,\"2014-04-29 00:00:00\",b)"} > > > Now I want to execute that query via dblink on a remote server. How do I > specify the result type? > > select tb.* from dblink( 'dbname=postgres', $$ > select array_agg(r) > from (values (1::int, 'today'::timestamp, 'a'::text), > $$) tb( WHAT DO I PUT HERE? ) > > Thanks, > Torsten ISTM that you have to "CREATE TYPE ..." as appropriate then ... tb ( col_alias type_created_above[] ) There is only so much you can do with anonymous types (which is what the ROW construct creates; ROW is not a type but an expression anchor - like ARRAY[...]) that tells the parser how to interpret what follows. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/arrays-of-rows-and-dblink-tp5802035p5802050.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] arrays of rows and dblink
On 30/04/14 20:19, David G Johnston wrote: > ISTM that you have to "CREATE TYPE ..." as appropriate then > > ... tb ( col_alias type_created_above[] ) > > There is only so much you can do with anonymous types (which is what the ROW > construct creates; ROW is not a type but an expression anchor - like > ARRAY[...]) that tells the parser how to interpret what follows. I thought so. Do I have to create the type in both databases or only on the receiving site? Thanks, Torsten -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] arrays of rows and dblink
Torsten Förtsch wrote > On 30/04/14 20:19, David G Johnston wrote: >> ISTM that you have to "CREATE TYPE ..." as appropriate then >> >> ... tb ( col_alias type_created_above[] ) >> >> There is only so much you can do with anonymous types (which is what the >> ROW >> construct creates; ROW is not a type but an expression anchor - like >> ARRAY[...]) that tells the parser how to interpret what follows. > > I thought so. Do I have to create the type in both databases or only on > the receiving site? No idea but it would probably be a good idea to create the type on both sides regardless of what is required. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/arrays-of-rows-and-dblink-tp5802035p5802054.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] arrays of rows and dblink
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 04/30/2014 12:52 PM, Torsten Förtsch wrote: > Hi, > > we have the ROW type and we have arrays. We also can create arrays > of rows like: > > select array_agg(r) from (values (1::int, 'today'::timestamp, > 'a'::text), (2, 'yesterday', 'b')) r(a,b,c); array_agg > --- > > {"(1,\"2014-04-30 00:00:00\",a)","(2,\"2014-04-29 00:00:00\",b)"} > > > Now I want to execute that query via dblink on a remote server. How > do I specify the result type? > > select tb.* from dblink( 'dbname=postgres', $$ select array_agg(r) > from (values (1::int, 'today'::timestamp, 'a'::text), $$) tb( WHAT > DO I PUT HERE? ) select * from dblink('dbname=test',$$select array_agg(r) from (values (1::int, 'today'::timestamp, 'a'::text), (2, 'yesterday', 'b')) r(a,b,c)$$) as d(f text[]); f - --- {"(1,\"2014-04-30 00:00:00\",a)","(2,\"2014-04-29 00:00:00\",b)"} (1 row) HTH, Joe - -- Joe Conway credativ LLC: http://www.credativ.us Linux, PostgreSQL, and general Open Source Training, Service, Consulting, & 24x7 Support -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.14 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/ iQIcBAEBAgAGBQJTYX7vAAoJEDfy90M199hl7/AQAICrBJILdmvO0Yut+nB+WLzX f2zFQWbavnM+NTB0oCTCAm5u8ivadrSPJbOc+tXUZ9HNS+RLTwhH4e4WEbW/xoVy yMJQH17tMKtk11huL08YvqyVTg5fafDcpUZ9I64u6S0jTHx8q1+um9iq2D/ssSkI xu5AF1YYRHFdLPJ0ifIMEi20ArxZb1BKo8EXi+EiW7ZGhX5LE24Q4CjrUdTeQZyq u3kwmZwkLni7ISgJqR9ChXo3KiOTprPZD2uejuQr3ivL8addCVVMDq6EAa7S0a/i Uff/P/HQyfgs5pNhBq0JR6ReoRE2B6Fmx2z/5VGctaNu23694nmJjH5xr9GeZT/G UXDBUUCQCX+ryErwQg0P2TjwiTp40BcZeuLpgBzHdep+LVcU/I/3zB5GrJz3ujLk BrzRGSr6FTxi4PGf88wDnFJ0c10pls9tdW1krPHE369eSBNIQbOx9Gh24tfG3meR iqW5JarWAENB9yhVOAQDFJVp6+P+80UuHOICLXsk2Pd6S4ExtolsSkC+HXDYQTLg rbMGhgKlrACkSw8IX0lGjg/P1PnZkFjrMRVmXgZi6kK1YvFX19bU2czy9XHKEoLK Zq1U4hgS8xiyvglYrJpEuX7hs3l84zSpPyjIWGka7KAjRAxq6QwsgtEw8EACoadf /o3oEbxraYEDN5m8Dns4 =M8Hv -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general