Pages

Sunday, October 30, 2011

Return query from PostgreSQL function

  1. Create a user defined type or a dummy table object. Fields and data type of the fields need to be same as the returning fields value of the function.
CREATE type production_int_time_figures_func_type AS(
project varchar,
language varchar,
daily_duration_sec numeric,
week_duration_sec numeric,
tot_duration_sec numeric
);
     2. Create the function
(Function to get interviewed time in seconds by giving start and end dates as parameters0

CREATE OR REPLACE FUNCTION production_int_time_figures_func(run_date date, from_date date)
  RETURNS SETOF production_int_time_figures_func_type AS
$BODY$
DECLARE
r production_int_time_figures_func_type%rowtype;
BEGIN
FOR r IN 
SELECT timecard.project, UPPER(trim(timecard.language)) ,
sum(CASE
            WHEN (date(Run_Date) - date(timecard.end_date)) <= 1 AND (date(timecard.end_date) > date(From_date)) THEN timecard.duration
            ELSE 0::numeric END) AS daily_duration_sec,
sum(CASE
            WHEN (date(Run_Date) - date(timecard.end_date)) <= 7 AND (date(timecard.end_date) > date(From_date)) THEN timecard.duration
            ELSE 0::numeric END) AS week_duration_sec,
sum(CASE WHEN (date(timecard.end_date) > date(From_date)) THEN timecard.duration ELSE 0::numeric END) AS tot_duration_sec
FROM accounts.timecard
WHERE (timecard.bcode::integer IN
(SELECT production_tasks_lookup.bcode FROM production_tasks_lookup
WHERE production_tasks_lookup.task_type::text = UPPER(TRIM('INT'::text))))
AND (timecard.project::text IN ( SELECT prodrep.project::character varying AS project
FROM accounts.prodrep))
GROUP BY timecard.project, UPPER(trim(timecard.language))
ORDER BY timecard.project, UPPER(trim(timecard.language))
LOOP 
RETURN NEXT r;
END LOOP;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE


CREATE OR REPLACE FUNCTION production_int_time_figures_func(run_date date, from_date date)
  RETURNS SETOF production_int_time_figures_func_type AS
$BODY$
DECLARE
r production_int_time_figures_func_type%rowtype;
BEGIN
FOR r IN 
SELECT ....... FROM ..........
LOOP 
RETURN NEXT r;
END LOOP;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE

    3. Retrieve data from function
select * FROM production_int_time_figures_func('2011-10-12', '2009-09-12');