Compare two columns from different table and make the insertion

Question

I have two tables:

1.Tbl_project

fld_id fld_allocated_days
1       10
2        3
3        1
4       99

2.Tbl_project_timesheet

fld_id fld_allocated_time fld_project_id
1       8.00                1
2       8.00                1
3       8.00                2
4       8.00                3
5       8.00                2
6       8.00                2
7       8.00                1
8       8.00                4
9       8.00                1

--fld_project_id references on (fld_id) from tbl_project

I want to make a function which compare the column fld_allocated_days from table tbl_project with column SUM(fld_allocated_time) from tbl_project_timesheet.

If the first column(fld_allocated_time) is lower than second(fld_allocated_days) make an insertion on table tbl_project_timesheet.

One of function parameters is p_project_id which will choose for what project to make the insertion of allocated time.

I have a function which make the insert of allocated time but don't check if exceeds the allocated days.

CREATE OR REPLACE FUNCTION function_add_timesheet_record( p_project_id integer, p_allocated_time numeric)
  RETURNS void AS

BEGIN

INSERT INTO tbl_project_timesheet(fld_project_id,fld_allocated_time)
    VALUES (p_project_id, p_allocated_time);
END

Answer 1

Based on the comments above, we make the assumption that 1 day from tbl_project equals 8 hours from table tbl_project_timesheet. That way taking the data you provided there are 6 missing days for project 1 and 98 missing days for project 4. We convert those to seconds and compare the values from both tables.

Then we fill an array with 8ths for the amount of whole days, and append the array with the remainder of the division (in case there are incomplete days).

Then we unnest the array, so that we have 8 hours per each row (day). And finally we insert the data.

WITH
  d AS (
    SELECT
      p.fld_id,
      p.fld_allocated_days * 8 * 3600 - COALESCE(
        SUM(
          EXTRACT(epoch FROM t.fld_allocated_time)::int
        ),
      0) AS diff
    FROM
      tbl_project p LEFT JOIN tbl_project_timesheet t
    ON
      p.fld_id=t.fld_project_id
    GROUP BY
      p.fld_id
  ),
  agg_d AS (
    SELECT
      fld_id,
      UNNEST(
        ARRAY_APPEND(
          ARRAY_FILL(
            '8:00'::time,
            ARRAY[(diff / (3600 * 8))::int]
          ),
          ((diff % (3600 * 8)) * interval '1 second')::time
        )
      ) AS hours FROM d
    WHERE
      diff > 0
  )
INSERT INTO tbl_project_timesheet
  (fld_project_id, fld_allocated_time)
SELECT
  fld_id, hours
FROM
  agg_d
WHERE
  hours > '0:0'::time;