On Jun 18, 2007, at 9:34 , Ranieri Mazili wrote:
Hello, I'm trying do the following function:
CREATE OR REPLACE FUNCTION lost_hours_temp(date)
RETURNS text AS
$BODY$
DECLARE
START_DATE date;
END_DATE date;
QUERY text;
BEGIN
START_DATE := $1;
END_DATE := START_DATE - interval '3 year';
WHILE EXTRACT(YEAR FROM START_DATE) = EXTRACT(YEAR FROM
END_DATE)+3 LOOP
QUERY := 'SELECT CAST(EXTRACT(YEAR FROM
A.production_date) || '/' || EXTRACT(MONTH FROM A.production_date)
AS TEXT) as date,
SUM(production_hours) AS production_hours,
B.id_production_area
FROM production A, product B
WHERE EXTRACT(MONTH FROM production_date) = EXTRACT
(MONTH FROM ' || START_DATE || ')
AND EXTRACT(YEAR FROM A.production_date) = EXTRACT
(YEAR FROM ' || START_DATE || ')
AND lost_hours = ' || 'S' ||'
AND A.id_product = B.id_product
GROUP BY id_production_area, date';
START_DATE := START_DATE - interval '1 month';
END LOOP;
RETURN QUERY;
END;
$BODY$
LANGUAGE 'plpgsql';
My problem is into WHILE, I'm trying to concatenate variables with
the string, but I guess that it's generating an error.
It looks like you've got a number of problems here, but overall it
appears you're approaching this from the wrong way. What's the final
result you want? I doubt it's just a query string. You probably want
to run this query somewhere, and you can do this from within PL/
pgSQL. You may want to look up set returning functions.
Looking over your function, I'm a little confused about what you're
trying to do. I'm guessing the (final) result you're trying to get is
the number of hours lost for each product per area per month for the
three years prior to the provided date. You should be able to do this
in just a single SQL query, something like:
SELECT date_trunc('month', production.production_date)::date
AS production_period
, product.id_production_area
, sum(production_hours) as total_production_hours
FROM production
JOIN product USING (id_product)
WHERE lost_hours = 'S'
AND date_trunc('month', a.production_date)::date BETWEEN
date_trunc('month', ? - 3 * interval '1 year')::date
AND date_trunc('month', ?)::date;
Things that were puzzling to me about your code:
* START_DATE is *after* END_DATE (END_DATE := START_DATE -
INTERVAL '3 year')
* WHILE EXTRACT(YEAR FROM START_DATE) = EXTRACT(YEAR FROM
END_DATE)+3 LOOP will only be true for a limited number of months,
not over the whole three-year range. The idea of three years has no
real meaning in the query after this point.
Anyway, hope this helps.
Michael Glaesemann
grzm seespotcode net
