有MySQL有pgsql怎么同时查询_mysql和pgsql夸库查询插入

当mysql中用到pgsql表的垮库操作时，首先可以使用ETL是可以解决的，今天学习了一种代码处理方式解决方案。

insert_sql = <

insert into #{audience_attributes_table}

select opxpid, "c", array_to_string(jx_array_sort(madlib.array_agg(pretargeting_id)), ',')

from (

select mt.opxpid as opxpid, ap.pretargeting_id as pretargeting_id

from #{master_table} mt

inner join

#{map_audiences_pretargetings}

on mt.hash_value = ap.audience_id

where mt.hash_key = 'aids'

and mt.hash_value in (#{aids.uniq.join(",")})

group by 1,2

) m

group by 1, 2;

sql

#拼装查询结果。

def map_audiences_pretargetings

pids = Pretargeting.find(:all, :select => :id, :conditions => ["start_date <= ? AND end_date >= ? AND approval_status = ?", Date.today + 3.days, Date.today - 3.days, true])

adjustments = AudiencesPretargeting.find_all_by_pretargeting_id(pids.collect(&:id)).collect{|ad_pg| [ad_pg.pretargeting_id,ad_pg.audience_id]}

adjustments_flattened = adjustments.map{ |adj| ",(#{adj[0]}, #{adj[1]})" }

"(

VALUES

(0, 0)

#{adjustments_flattened.join}

) AS ap (pretargeting_id, audience_id)"

end