CODEWITHSUNDEEP
sqlfunctionpostgresqlstored-procedures
Alienware
Alienware

Reputation: 321

Error column reference is ambiguous

I have a function which:

  1. compare two columns from different tables

  2. make the insert

How can I get some improvement on this code because I get this error:

ERROR:  column reference "fld_id" is ambiguous
SQL state: 42702
line 17 at PERFORM

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 void AS
$BODY$

BEGIN
DECLARE sum_alloc_time numeric; 
DECLARE alloc_hours integer;
DECLARE fld_id integer;

    SELECT @sum_alloc_time = SUM(fld_allocated_time)
    from "SD_PRJ".tbl_project_timesheet 
    where fld_project_id = p_project_id;

    SELECT @alloc_hours = p.fld_allocated_days, @fld_id = p.fld_id
    FROM "SD_PRJ".tbl_project p
    INNER JOIN "SD_PRJ".tbl_project_timesheet t
    ON p.fld_id=t.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;

$BODY$ LANGUAGE plpgsql;

There are two tables:

"SD_PRJ".tbl_project (
  fld_id
 ,fld_allocated_days)

"SD_PRJ".tbl_project_timesheet (
  fld_id
, fld_project_id
,fld_allocated_time)

Upvotes: 2

Views: 4291

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657727

Working version:

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 varchar, p_obs varchar, p_date timestamp)
  RETURNS void AS
$func$
DECLARE
   _sum_alloc_time numeric; 
   _alloc_hours integer;
   _fld_id integer;
BEGIN
   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);
      -- alloc_id is undefined, you probably need to use SELECT .. FROM .. instead

      RAISE NOTICE 'INSERT OK!';

   ELSE
      RAISE NOTICE 'NOT OK';
   END IF;
END
$func$ LANGUAGE plpgsql;

But this is still needlessly inefficient. Could be done in a single DML statement with data-modifying CTEs. Try a search for related questions.

You need to study the basics first.

Upvotes: 1

Related Questions