perl/generate_upsert.pm

   1 # WITH new_values (chr,pos,ref_allele,alt_allele,rsid) AS (
   2 #   VALUES
   3 #      (?,?,?,?,?)
   4 # ),
   5 # upsert AS
   6 # (
   7 #     UPDATE snpinfo s
   8 #         SET ref_allele = nv.ref_allele,
   9 #             alt_allele = nv.alt_allele,
  10 #                   rsid = nv.rsid::integer
  11 #     FROM new_values nv
  12 #     WHERE s.chr::text = nv.chr AND s.pos = nv.pos::integer
  13 #     RETURNING s.*
  14 # )
  15 # INSERT INTO snpinfo (chr,pos,ref_allele,alt_allele,rsid)
  16 # SELECT chr,pos::integer,ref_allele,alt_allele,rsid::integer
  17 # FROM new_values
  18 # WHERE NOT EXISTS (SELECT 1
  19 #                   FROM upsert up
  20 #                   WHERE up.chr::text = new_values.chr AND up.pos = new_values.pos::integer);
  21
  22 use warnings;
  23 use strict;
  24
  25 package generate_upsert;
  26
  27 use Params::Validate qw(validate_with :types);
  28
  29 sub generate_upsert{
  30     my %param = validate_with(params => \@_,
  31                               spec => {table => SCALAR,
  32                                        keys  => {type => ARRAYREF,
  33                                                 },
  34                                        columns => {type => ARRAYREF,
  35                                                   },
  36                                       },
  37                              );
  38     my @keys = map {ref($_)?$_->[0]:$_} @{$param{keys}};
  39     my @cols = map {ref($_)?$_->[0]:$_} @{$param{columns}};
  40     my %cols_types;
  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);
  48 return <<"END";
  49 WITH new_values ($cols) AS (
  50   VALUES
  51      ($placeholders)
  52 ),
  53 upsert AS
  54 (
  55     UPDATE $param{table} t
  56         SET $set
  57     FROM new_values
  58     WHERE $where_set
  59     RETURNING t.*
  60 )
  61 INSERT INTO $param{table} ($cols)
  62 SELECT $cols_types
  63 FROM new_values
  64 WHERE NOT EXISTS (SELECT 1
  65                   FROM upsert up
  66                   WHERE $where_up);
  67 END
  68 }
  69
  70
  71 1;