How do I run SELECT statement inside DO-IF-INSERT statement in Postgresql?-CodePudding

I need to insert values into a global table based on whether local table (which is updated everyday) is empty or not. So I tried this code, and it totally works if I replace the second SELECT statement with actual values, but those values will be changing every day, so I really need to find some workaound.

DO
'
BEGIN
   IF ((SELECT count(*) FROM crit_crashes) > 0) THEN
      INSERT INTO usr_wrk.crit_crashes (sd, title, business_line, start_dttm, end_dttm, category, date_opened) VALUES (SELECT * FROM crit_crashes); 
   ELSE
      INSERT INTO usr_wrk.crit_crashes (sd, title, business_line, start_dttm, end_dttm, category, date_opened) VALUES (NULL, NULL, NULL, NULL, NULL, NULL, current_date); 
   END IF;
END;
'

CodePudding user response：

No need for PL/pgSQL or an IF statement. You can use a SELECT statement that returns at least one row:

INSERT INTO usr_wrk.crit_crashes (sd, title, business_line, start_dttm, end_dttm, category, date_opened) 
SELECT sd, title, business_line, start_dttm, end_dttm, category, date_opened
FROM crit_crashes
UNION ALL
SELECT NULL, NULL, NULL, NULL, NULL, NULL, current_date
WHERE NOT EXISTS (select * from crit_crahes);

If no rows exist in crit_crashes then the first part of the UNION won't return anything, and the second will return a single row. If there are rows in the table, the second part won't return anything so only the rows from crit_crashes are inserted.