Re: [GENERAL] Setting Variable - (Correct) - Mailing list pgsql-sql

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