Compare two columns and make the insert

Question

I want to compare two columns which come from two different tables.

One of the columns, I need to make SUM for all rows with identity let's say 3 and store to a variable. After that, compare with one row from other table for same identity 3 and to INSERT something ELSE to BREAK if first_column <= second_column.

Can someone suggest some query for this? For Postgresql...

CREATE OR REPLACE FUNCTION "SA_PRJ".usp_add_timesheet_test(p_uid integer, p_project_id integer, p_allocated_time numeric, p_achieved_time numeric, p_task_desc character varying, p_obs character varying, p_date timestamp without time zone)
  RETURNS character varying AS
$BODY$
DECLARE sum_alloc_time numeric; 
DECLARE alloc_hours integer;
DECLARE fld_id integer;
DECLARE alloc_id integer;

BEGIN
   if not "SA_ADM".usp_check_permission(p_uid, 'SA_PRJ', 'usp_add_timesheet_record') then
    raise exception 'User ID % dont have permission!', p_uid;
   end if;

   select a.fld_id into alloc_id from "SD_PRJ".tbl_project_allocation a where a.fld_emp_id = p_uid and a.fld_project_id = p_project_id;

   SELECT SUM(fld_allocated_time)
   INTO           sum_alloc_time 
   FROM   "SD_PRJ".tbl_project_timesheet 
   WHERE  fld_project_id = p_project_id;

   SELECT p.fld_allocated_days, p.fld_id
   INTO            alloc_hours,   fld_id 
   FROM   "SD_PRJ".tbl_project p
   JOIN   "SD_PRJ".tbl_project_timesheet t USING (fld_id)
   WHERE  t.fld_project_id = p_project_id;

  IF @sum_alloc_time <= @alloc_hours THEN

  INSERT INTO "SD_PRJ".tbl_project_timesheet
    (fld_emp_id, fld_project_id, fld_is_allocated,   fld_allocated_time
   , fld_achieved_time, fld_task_desc, fld_obs, fld_date)
  VALUES (p_uid,   p_project_id, coalesce(alloc_id,0), p_allocated_time
     , p_achieved_time,   p_task_desc,   p_obs,   p_date);

     RAISE NOTICE 'INSERT OK!';

 ELSE
  RAISE NOTICE 'NOT OK';
 END IF;
END

1.tbl_project (fld_id, fld_allocated_days,fld_project_id)

2.tbl_project_timesheet(fld_id,fld_allocated_time,fld_project_id), all INTEGER

I have this , but dosen't work as I wish.Thanks

It would help if you describe with sample data what your tables look like.. — Ahmad, Nov 11 '14 at 12:35
You must escape your quotes in the content. "don't" should be "don''t", using 2 single quotes '. See http://www.postgresql.org/docs/current/static/plpgsql-development-tips.html#PLPGSQL-QUOTE-TIPS — Frank Heikens, Nov 11 '14 at 12:44
What does "doesn't work" mean? Make a simplified version, and test the individual bits. One tip - all these local variables you're using - you can get rid of them all and just use joins. If you post a simplified procedure I (or someone else) can give you an example. — mlinth, Nov 11 '14 at 12:55

score 1 · Accepted Answer · edited May 23 '17 at 12:21

I think one problem is here:

 SELECT p.fld_allocated_days, p.fld_id
   INTO            alloc_hours,   fld_id 
   FROM   "SD_PRJ".tbl_project p
   JOIN   "SD_PRJ".tbl_project_timesheet t USING (fld_id)
   WHERE  t.fld_project_id = p_project_id;

That will cough (I think) whenever the select query returns more than one row i.e. whenever tbl_project_timesheet has more than one record for a fld_id,project_id combination.

Anyway. Here's a partial, simplified answer, but hopefully you get the idea...

I wouldn't use local variables. Do the insert in one step:

INSERT INTO timesheet(emp_id,project_id) -- other columns
SELECT 
    p_uid,p.fld_project_id -- other columns
FROM

    projects p

INNER JOIN
    (SELECT SUM(fld_allocated_time) as sumtime
        FROM timesheet t WHERE fld_project_id = p_project_id) as sumtime_subquery
ON p.fld_allocated_days < sumtime -- just join on the allocated time
WHERE p.fld_project_id = p_project_id;

Now, you need to know if anything was actually inserted. I think you can use the RETURNING option of the INSERT statement, e.g. from here (caveat - I have never used RETURNING, nor set a local variable from a with statement):

WITH ROWS AS (
 INSERT INTO timesheet(emp_id,project_id) -- other columns
    SELECT 
        p_uid,p.fld_project_id -- other columns
    FROM

        projects p

    INNER JOIN
        (SELECT SUM(fld_allocated_time) as sumtime
            FROM timesheet t WHERE fld_project_id = p_project_id) as sumtime_subquery
    ON p.fld_allocated_days < sumtime -- just join on the allocated time
    WHERE p.fld_project_id = p_project_id
    RETURNING 1
    )
    SELECT COUNT(*) into l_updatedCount FROM rows; -- you have to declare l_updatedCount

-- Now an if statement to handle l_updatedCount

Compare two columns and make the insert

1 Answers1