1 # WITH new_values (chr,pos,ref_allele,alt_allele,rsid) AS (
8 # SET ref_allele = nv.ref_allele,
9 # alt_allele = nv.alt_allele,
10 # rsid = nv.rsid::integer
12 # WHERE s.chr::text = nv.chr AND s.pos = nv.pos::integer
15 # INSERT INTO snpinfo (chr,pos,ref_allele,alt_allele,rsid)
16 # SELECT chr,pos::integer,ref_allele,alt_allele,rsid::integer
18 # WHERE NOT EXISTS (SELECT 1
20 # WHERE up.chr::text = new_values.chr AND up.pos = new_values.pos::integer);
25 package generate_upsert;
27 use Params::Validate qw(validate_with :types);
30 my %param = validate_with(params => \@_,
31 spec => {table => SCALAR,
32 keys => {type => ARRAYREF,
34 columns => {type => ARRAYREF,
38 my @keys = map {ref($_)?$_->[0]:$_} @{$param{keys}};
39 my @cols = map {ref($_)?$_->[0]:$_} @{$param{columns}};
41 @cols_types{@keys,@cols} = map {ref($_)?($_->[0].'::'.$_->[1]):$_} @{$param{keys}},@{$param{columns}};
42 my $cols = join(', ',@keys,@cols);
43 my $cols_types = join(', ',map{$cols_types{$_}} @keys, @cols);
44 my $placeholders = join(', ',('?') x (@cols + @keys));
45 my $set = join(",\n",map {qq($_ = new_values.$cols_types{$_})} @cols);
46 my $where_up = join(' AND ',map {qq(up.$cols_types{$_} = new_values.$cols_types{$_})} @keys);
47 my $where_set = join(' AND ',map {qq(t.$cols_types{$_} = new_values.$cols_types{$_})} @keys);
49 WITH new_values ($cols) AS (
55 UPDATE $param{table} t
61 INSERT INTO $param{table} ($cols)
64 WHERE NOT EXISTS (SELECT 1