Design (code) a stored procedure that can be used to refresh the data in both your detailed and summary tables. The procedure should clear the contents of the detailed and summary tables and perform the ETL load process from part C and include comments that identify how often the stored procedure should be executed.

1.  Explain how the stored procedure can be run on a schedule to ensure data freshness.

 

This is done in postgreSQL. I do NOT need to set up the stored procedure to run on a timer I just need to explain how I could set it to run on a timer. I would be using 30 day timer to have the report refreshed for each meeting. 

 

I attempted to code a stored procedure already but I am getting an error and do not know where I went wrong. The function works fine when I run the code, but errors when I try to call the function. Here is a SS of what I have so far.

 

 

Transcribed Image Text:

Query Editor Query History CREATE OR REPLACE PROCEDURE refresh_tables() language plpgsql 1 2 3 AS $$ 4 begin 5 DELETE FROM employee_rev_detailed; INSERT INTO employee_rev_summary 6 DELETE FROM employee_rev_summary; 7 8 9 10 11 FROM employee_rev_summary SELECT staff_id, employee_name(first_name, last_name) AS full_name, SUM (amount) as employee_rev_summary 12 WHERE payment_date >= (current_date - 30000) 13 GROUP BY staff_id, first_name, last_name; 14 16 17 15 INSERT INTO employee_rev_detailed SELECT staff_id, employee_name(first_name, last_name) AS full_name, (amount) as employee_sales, payment_date 18 FROM employee_rev_detailed 19 WHERE payment_date >= (current_date - 30000) 20 21 22 23 24 $$; GROUP BY staff_id, first_name, last_name, payment_date, amount; RETURN; end; 25 26 CALL refresh_tables(); 27 SELECT * FROM employee_rev_detailed Data Output Explain Messages Notifications LINE 2: SELECT staff_id, employee_name(first_name, last_name) AS ful... A QUERY: INSERT INTO employee_rev_summary SELECT staff_id, employee_name (first_name, last_name) AS full_name, SUM (amount) as employee_rev_summary FROM employee_rev_summary WHERE payment_date >= (current_date - 30000) GROUP BY staff_id, first_name, last_name CONTEXT: PL/pgSQL function refresh_tables() line 6 at SQL statement SQL state: 42703 Scratch Pad Query Editor Query History CREATE OR REPLACE PROCEDURE refresh_tables() language plpgsql 1 2 3 AS $$ 4 begin 5 DELETE FROM employee_rev_detailed; INSERT INTO employee_rev_summary 6 DELETE FROM employee_rev_summary; 7 8 9 10 11 FROM employee_rev_summary SELECT staff_id, employee_name(first_name, last_name) AS full_name, SUM (amount) as employee_rev_summary 12 WHERE payment_date >= (current_date - 30000) 13 GROUP BY staff_id, first_name, last_name; 14 16 17 15 INSERT INTO employee_rev_detailed SELECT staff_id, employee_name(first_name, last_name) AS full_name, (amount) as employee_sales, payment_date 18 FROM employee_rev_detailed 19 WHERE payment_date >= (current_date - 30000) 20 21 22 23 24 $$; GROUP BY staff_id, first_name, last_name, payment_date, amount; RETURN; end; 25 26 CALL refresh_tables(); 27 SELECT * FROM employee_rev_detailed Data Output Explain Messages Notifications LINE 2: SELECT staff_id, employee_name(first_name, last_name) AS ful... A QUERY: INSERT INTO employee_rev_summary SELECT staff_id, employee_name (first_name, last_name) AS full_name, SUM (amount) as employee_rev_summary FROM employee_rev_summary WHERE payment_date >= (current_date - 30000) GROUP BY staff_id, first_name, last_name CONTEXT: PL/pgSQL function refresh_tables() line 6 at SQL statement SQL state: 42703 Scratch Pad