Search Postgresql Archives

Setting Variable - (Correct)

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.
What's the correct form to concatenate strings with query in my case?

Thanks


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux