0

I have a bunch of tables that all have a storenumber, extracteddate and extractedtime column.

I'm doing ETL on these tables and I want to put the last extracted date and time for each store in a single table, with an additional column for the input table name. So I've ended up repeating the same upsert a bunch of times and really the only thing that is different is the CTE that I am using for input:

with rows as (
  select distinct on(storenumber)
         storenumber, extracteddate, extractedtime
    from storefinancialdate
  )
  insert into star.stat_etl (storenumber, filename, last_extracted, last_loaded)
    select rows.storenumber,
           'storefinancialdate',
           (rows.extracteddate + rows.extractedtime::time),
           transaction_timestamp()
      from rows
    on conflict(storenumber, filename) do update
      set last_extracted = excluded.last_extracted,
          last_loaded    = excluded.last_loaded;

with rows as (
  select distinct on(storenumber)
         storenumber, extracteddate, extractedtime
    from storeproduct
)
insert into star.stat_etl (storenumber, filename, last_extracted, last_loaded)
  select rows.storenumber,
         'storeproduct',
         (rows.extracteddate + rows.extractedtime::time),
         transaction_timestamp()
    from rows
  on conflict(storenumber, filename) do update
    set last_extracted = excluded.last_extracted,
        last_loaded    = excluded.last_loaded;

-- repeat a bunch more times...

Is there some way that I can DRY this up? I can't pass a CTE into a function, can I?

(I get that I could call a function on each record of each CTE, one at a time. That would make the code shorter but also much, much slower. So I don't want to do that.)

2
  • First, it's not clear what the "repeat a bunch of time" would be. Does only the table name change? Second, the distinct on () in the cte is not deterministic. There should be an order by there. Commented Jul 14, 2016 at 11:45
  • @ypercube -- re: distinct -- oops, well caught. re: what changes -- see expanded example above.
    – Andy Jones
    Commented Jul 14, 2016 at 12:00

1 Answer 1

3

You could use UNION in the CTE. There is still some duplication of code but less than before:

with etl (storenumber, filename, last_extracted) as
(
    ( select distinct on (storenumber)
          storenumber, 
          'storefinancialdate',
          extracteddate + extractedtime::time 
      from storefinancialdate
      order by
          storenumber,
          extracteddate desc, extractedtime desc
    )
  union all
    ( select distinct on (storenumber)
          storenumber, 
          'storeproduct',
          extracteddate + extractedtime::time 
      from storeproduct
      order by
          storenumber,
          extracteddate desc, extractedtime desc
    )
  -- union all ...
)
insert into star.stat_etl 
    (storenumber, filename, last_extracted, last_loaded)
select 
    storenumber,
    filename,
    last_extracted,
    transaction_timestamp()
from etl
  on conflict (storenumber, filename) do update
    set last_extracted = excluded.last_extracted,
        last_loaded    = excluded.last_loaded ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.