Building query in PL/pgsql with different columns based on flag

Question

I need to run the following statement in PL/pgSQL.

    if file_record.has_recieved_time_column then
        EXECUTE FORMAT('COPY mytemp (columnA, columnB, columnC)
                        FROM %L
                        DELIMITER '',''
                        CSV HEADER;', file_record.file_path);
    elseif file_record.has_header then
        -- dont load columnC
        EXECUTE FORMAT('COPY mytemp (columnA, columnB)
                        FROM %L
                        DELIMITER '',''
                        CSV HEADER;', file_record.file_path);
    else
        -- dont load columnC and the file has no header
        EXECUTE FORMAT('COPY mytemp (columnA, columnB)
                        FROM %L
                        DELIMITER '',''
                        CSV;', file_record.file_path);
    end if;

How can I avoid repeating repeating myself in this code?

You have three different COPY statements. What do you mean by repeating? Does the function goes on with multiple IF / ELSE ? — Jim Jones
– Jim Jones, Commented May 12, 2021 at 11:57
Ideally I would like to build up the query by somehow defining cols = (colA, colB) then do if file_record.has_recieved_time_column: cols += colC and then pass cols as a parameter to the COPY statement and do something similar for the HEADER — sev
– sev, Commented May 12, 2021 at 12:06

Jim Jones · Accepted Answer · 2021-05-12 12:23:47Z

1

Try creating variable cols with default values columnA,columnB and then contatenate it with columnC in case you need it, e.g.

  cols := 'columnA, columnB';
  
  IF file_record.has_recieved_time_column THEN
    cols = cols || ',columnC';
  ELSEIF file_record.has_header THEN
    cols = cols || ',columnX';
  ELSE
    .... 
  END IF;
  
  EXECUTE FORMAT('COPY mytemp (%L) FROM %L DELIMITER '',''CSV HEADER;', cols, file_record.file_path);

answered May 12, 2021 at 12:23

Jim Jones

19.9k3 gold badges44 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

sev Over a year ago

This is exactly what I was looking for, is there a way to also solve the header problem in the same way, can I set a header variable which is an empty string and replace it with HEADER then use %L in the same way as you did now?

Jim Jones Over a year ago

@sev absolutely. Just replace the HEADER in the COPY string with %L and add an extra variable in the end of the FORMAT command.

Jim Jones Over a year ago

@sev something like EXECUTE FORMAT('COPY mytemp (%L) FROM %L DELIMITER '',''CSV %L;', cols, file_record.file_path,header_variable);

Collectives™ on Stack Overflow

Building query in PL/pgsql with different columns based on flag

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related