Postgres Pro
Downloads
Home   >   mailing lists

Thread: arrays of rows and dblink

arrays of rows and dblink

From
Torsten Förtsch
Date:
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

Re: arrays of rows and dblink

From
David G Johnston
Date:
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.

Re: arrays of rows and dblink

From
Torsten Förtsch
Date:
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

Re: arrays of rows and dblink

From
David G Johnston
Date:
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.

Re: arrays of rows and dblink

From
Joe Conway
Date:
-----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-----