Pages

Monday, November 7, 2011

Temporary table inside a PostgreSQL function

1. First, create a TYPE Object defining returning fields.
CREATE TYPE accounts.weekly_payroll_report_type AS(
batch integer,
project varchar,
language varchar,
intcode VARCHAR,
bcode integer,
description VARCHAR,
p_tot_hrs NUMERIC,
pay_amount NUMERIC );
2. Create the function
CREATE OR REPLACE FUNCTION accounts.weekly_payroll_report_indirect(integer)
RETURNS setof accounts.weekly_payroll_report_type
AS $$
DECLARE
int_payrate NUMERIC;
 cur_timecard CURSOR FOR select t.batch , t.language, t.project, t.intcode AS t_intcode,
FROM accounts.timecard_duration_view t
INNER JOIN task k ON (k.bcode = t.bcode  AND  UPPER(k.billable) = UPPER(t.billable))
WHERE t.batch = $1 AND UPPER(k.billable) = 'Y'
GROUP BY 1, 2, 3, 4, 5 ,6
cur_pay CURSOR FOR SELECT batch, project, intcode as p_intcode , sum_hours AS p_tot_hrs FROM accounts.payroll_hours_view p WHERE batch = $1; 
BEGIN
CREATE TEMPORARY TABLE temp_payroll_val(
tmp_batch INTEGER, tmp_project VARCHAR,
tmp_p_tot_hrs NUMERIC, tmp_pay_amount NUMERIC) on commit drop;
FOR rec in cur_pay
LOOP FOR rec1 in cur_timecard LOOP
IF (rec.batch::INT = rec1.batch::INT AND rec.project::TEXT = rec1.project::TEXT AND rec.p_intcode = rec1.t_intcode) THEN
SELECT SUM(sum_duration) INTO sum_duration_per_intcode FROM accounts.timecard_duration_view
WHERE batch = rec1.batch AND project = rec1.project AND intcode =  rec1.t_intcode ; SELECT r.amount INTO int_payrate FROM accounts.payroll pp  INNER JOIN accounts.ratecode r ON (UPPER(pp.pay_rate) = UPPER(r.pay_rate))
INSERT INTO temp_payroll_val VALUES (rec.batch, rec.project, rec1.language, rec1.t_intcode, rec1.t_bcode, rec1.description, ROUND(prorated_hrs, 2), (int_payrate * ROUND(prorated_hrs, 2)));
  ELSE
prorated_hrs = 0;
END IF;
END IF;
END LOOP;
END LOOP;
FOR tem_val IN select * FROM temp_payroll_val
LOOP
RETURN NEXT tem_val;
END LOOP;
  RETURN ;
END;
$$ LANGUAGE plpgsql VOLATILE
2. Read the data from function         select * from accounts.weekly_payroll_report_indirect(201129);