| From: | Ranieri Mazili <ranieri(dot)oliveira(at)terra(dot)com(dot)br> |
|---|---|
| To: | pgsql-general(at)postgresql(dot)org, pgsql-sql(at)postgresql(dot)org |
| Subject: | Setting Variable - (Correct) |
| Date: | 2007-06-18 14:34:21 |
| Message-ID: | 467697ED.8030506@terra.com.br |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general 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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Tom Lane | 2007-06-18 14:42:22 | Re: pg_restore out of memory |
| Previous Message | Ranieri Mazili | 2007-06-18 14:29:43 | Setting variable |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Michael Glaesemann | 2007-06-18 14:43:55 | Re: [SQL] Setting variable |
| Previous Message | Ranieri Mazili | 2007-06-18 14:29:43 | Setting variable |