"Roberts, Jon" <Jon.Roberts@asurion.com> writes:
> If the remote database is on the same server, it works but fails on the
> different server. It looks like the datatype aclitem[] is linked to
> roles which seems weird to me.
Why would you find that weird? aclitem exists to store references to
roles.
> --doesn't work
> select t1.schema_name,
> t1.table_name,
> t1.table_owner,
> t1.relacl
> from dblink('dbname=test_dev_db user=scott password=tiger host=gp',
> 'select n.nspname as schema_name, c.relname as
> table_name, pg_get_userbyid(c.relowner) as table_owner, c.relacl from
> pg_class c join pg_namespace n on n.oid = c.relnamespace'::text)
> t1(schema_name name, table_name name, table_owner name,
> relacl aclitem[]);
> ERROR: role "gpadmin" does not exist
> User gpadmin doesn't exist on my local server.
Hmm, that's a bit annoying --- evidently, dblink is trying to cast the
text string coming from the remote server into a local aclitem[] value,
and aclitemin is (quite properly) barfing. So you need to get the
exposed type of the query result column to not be aclitem.
> It wouldn't let me convert aclitem to varchar.
8.3 would let you do that, but in older releases you're going to need
subterfuge. Try using aclitemout() ... or actually, since relacl is
aclitem[], you probably need array_out().
regards, tom lane