SELECT t.resourceid,
t.resourcename,
t.resourcealias,
t.resourcetype,
t.resourceurl,
t.groupid,
t.requesttype,
t.resourcedes,
t.resourceproxyurl
FROM dblink('dbname=resourceregister_cp hostaddr=192.9.30.27 port=5432 user=postgres password=postgres'::text, 'select distinct serviceid as resourceid,servicename as resourcename,servicealias as resourcealias,servicetype as resourcetype,serverurl as resourceurl, rcr.resourcegroupid as groupid ,''get'' as requesttype,description as resourcedes, serverurl_out as resourceproxyurl
from dbms_data_service ds inner join dbms_rea_catalog_resource rcr on ds.serviceid = rcr.resourceid
inner join share_resource sr on sr.sourceid=ds.serviceid and sr.audit_status=''1''
union all
select distinct interfaceid as resourceid,interfacename as resourcename,interfacealias as resourcealias,interfacetype as resourcetype,interfaceurl as resourceurl, rcr.resourcegroupid as groupid ,''get'' as requesttype,interfacedescription as resourcedes,interfaceproxyurl as resourceproxyurl
from dbms_data_interface di inner join dbms_rea_catalog_resource rcr on di.interfaceid = rcr.resourceid
inner join share_resource sr on sr.sourceid=di.interfaceid and sr.audit_status=''1''
union all
select distinct productid as resourceid,productname as resourcename,productalias as resourcealias,producttype as resourcetype,producturl as resourceurl ,rcr.resourcegroupid as groupid ,''get'' as requesttype,productdescription as resourcedes,productproxyurl as resourceproxyurl
from dbms_data_product dp inner join dbms_rea_catalog_resource rcr on dp.productid = rcr.resourceid
inner join share_resource sr on sr.sourceid=dp.productid and sr.audit_status=''1''
union all
select distinct fileid as resourceid,name as resourcename, alias as resourcealias,resourcetype as resourcetype,serverpath as resourceurl,rcr.resourcegroupid as groupid ,''get'' as requesttype,'''' as resourcedes,serverproxypath as resourceproxyurl
from dbms_unstru_file uf inner join dbms_rea_catalog_resource rcr on uf.fileid = rcr.resourceid
inner join share_resource sr on sr.sourceid=uf.fileid and sr.audit_status=''1'''::text) t(resourceid character varying, resourcename character varying, resourcealias character varying, resourcetype character varying, resourceurl character varying, groupid character varying, requesttype character varying, resourcedes character varying, resourceproxyurl character varying)