Thread: Setting Variable - (Correct)
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
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
On Jun 18, 2007, at 10:17 , Michael Glaesemann wrote:
> 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.
Or, rather, the number of hours lost per production area per month
for the three years prior to the provided date.
> 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;
Looks like I forgot the GROUP BY clause:
GROUP BY production_period, id_production_area
Michael Glaesemann
grzm seespotcode net
-------- Original Message --------
Subject: Re:[SQL] [GENERAL] Setting Variable - (Correct)
From: Michael Glaesemann <grzm@seespotcode.net>
To: Michael Glaesemann <grzm@seespotcode.net>
Date: 18/6/2007 13:15
>
> On Jun 18, 2007, at 10:17 , Michael Glaesemann wrote:
>
>> 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.
>
> Or, rather, the number of hours lost per production area per month for
> the three years prior to the provided date.
>
>> 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;
>
> Looks like I forgot the GROUP BY clause:
>
> GROUP BY production_period, id_production_area
>
> Michael Glaesemann
> grzm seespotcode net
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
>
Thanks a lot for your prompt reply.
You query is perfect for my problem, but I need another thing with it, I
need to return the sum of production_hours of each month of the current
year, and I need to return too the average of the 3 past years, can I do
all in only one query or I need to do a UNION with another query?
More one time, thanks a lot for your help.
[Please reply to the list so that others may benefit from and participate in the discussion.] On Jun 18, 2007, at 11:32 , Ranieri Mazili wrote: > Thanks a lot for your prompt reply. > You query is perfect for my problem, but I need another thing with > it, I need to return the sum of production_hours of each month of > the current year, and I need to return too the average of the 3 > past years, can I do all in only one query or I need to do a UNION > with another query? Glad you found it helpful. What have you tried so far? Michael Glaesemann grzm seespotcode net
-------- Original Message --------
Subject: Re:[SQL] [GENERAL] Setting Variable - (Correct)
From: Michael Glaesemann <grzm@seespotcode.net>
To: Ranieri Mazili <ranieri.oliveira@terra.com.br>
Date: 18/6/2007 13:50
> [Please reply to the list so that others may benefit from and
> participate in the discussion.]
>
> On Jun 18, 2007, at 11:32 , Ranieri Mazili wrote:
>
>> Thanks a lot for your prompt reply.
>> You query is perfect for my problem, but I need another thing with
>> it, I need to return the sum of production_hours of each month of the
>> current year, and I need to return too the average of the 3 past
>> years, can I do all in only one query or I need to do a UNION with
>> another query?
>
> Glad you found it helpful. What have you tried so far?
>
> Michael Glaesemann
> grzm seespotcode net
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
>
Look how I did:
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', production.production_date)::date BETWEEN
date_trunc('month', CAST('2007-06-18' AS date) - (EXTRACT(MONTH
FROM CAST('2007-06-18' AS date))-1) * interval '1 month')::date
AND date_trunc('month', CAST('2007-06-18' AS date))::date
GROUP BY production_period, id_production_area
UNION
SELECT date_trunc('year', production.production_date)::date
AS production_period
, product.id_production_area
, sum(production_hours)/12 as total_production_hours
FROM production
JOIN product USING (id_product)
WHERE lost_hours = 'S'
AND date_trunc('year', production.production_date)::date BETWEEN
date_trunc('year', CAST('2007-06-18' AS date) - 3 * interval '1
year')::date
AND date_trunc('year', CAST('2007-06-18' AS date) - 1 * interval
'1 year')::date
GROUP BY production_period, id_production_area
ORDER BY production_period DESC
I changed the "?" for values to test.
Look, I did a UNION, exist other way to do it better?
Thanks
On Jun 18, 2007, at 12:11 , Ranieri Mazili wrote:
> Look, I did a UNION, exist other way to do it better?
Considering your aggregates are different, you shouldn't really union
them. In the upper query of the union, you've got production_period
(which is actually a date that represents the beginning of a month-
long period), id_production_area, and an aggregate using sum as
total_production_hours. In the lower query of the union, you've got
production_period (a date representing the beginning of a year-long
period), id_production_area, and a aggregate representing monthly
average hours as total_production_hours. These are logically two
separate results, and should not be unioned. It's easier to see if
the columns are renamed appropriately:
SELECT production_month, id_production_area, monthly_production_hours
...
UNION
SELECT production_year, id_production_area,
monthly_average_production_hours
...
You can see that they're different. One consequence of this is that
for the query you have, you'll have more than on column with a date
'YYYY-01-01': is this a production_month or a production_year?
I guess I'd split it into two queries (and rename the columns). You
might also be able to join the to queries so you get a result
something like
SELECT production_year
, production_month
, id_production_area
, monthly_production_hours
, monthly_average_production_hours
Each month for the entire three-year range would be listed, and the
production_year and monthly_production_hours would be repeated for
each month of the year.
Yet another way to do it would be to create a view for
production_month, id_production_area, and monthly_production_hours
(with no restriction on date range), and then call the view twice:
once for the monthly figures for a year:
SELECT production_month, id_production_area, monthly_production_hours
FROM monthly_production
WHERE production_month BETWEEN date_trunc('month', ? - interval '1
year') AND date_trunc('month', ?);
and once more for the yearly figures for the past three:
SELECT date_trunc('year', production_month) as production_year
, sum(production_month) as number_of_months -- so you can see if you
have a full twelve-months
, id_production_area
, average(monthly_production_hours)
FROM monthly_production
WHERE date_trunc('year', production_month)
GROUP BY -- left as an exercise for the reader :)
Note that if you don't have any lost hours for a given year, you may
have some surprising results. You might want to look at
generate_series or some other solution for generating a full list of
months for you to join against.
By the way, if you're going to do a lot of the same date_trunc work,
you might want to create some functions that do this for you, e.g.
(untested),
CREATE FUNCTION trunc_year(date)
RETURNS date
IMMUTABLE
LANGUAGE sql AS $_$
select date_trunc('year', $1)::date
$_$;
CREATE FUNCTION truc_years_ago(date, integer)
RETURNS date
IMMUTABLE
LANGUAGE sql AS $_$
select date_trunc('year', $1 - $2 * INTERVAL '1 year')::date
$_$:
Note that foo::date is PostgreSQL-specific for CAST(foo AS DATE).
Anyway, hope that gives you something to think about.
Michael Glaesemann
grzm seespotcode net