Setting Variable - (Correct)
От | Ranieri Mazili |
---|---|
Тема | Setting Variable - (Correct) |
Дата | |
Msg-id | 467697ED.8030506@terra.com.br обсуждение исходный текст |
Ответы |
Re: [GENERAL] Setting Variable - (Correct)
|
Список | pgsql-sql |
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
В списке pgsql-sql по дате отправления: