[SQL] monthly statistics

[Andreas]

Hi,

I need to show a moving statistic of states of objects for every month 
since beginning of 2013.


There are tables like
objects ( id integer, name text );
state ( id integer, state text );   10=A, 20=B ... 60=F
history ( object_id integer, state_id, ts timestamp );

Every event that changes the state of an object is recorded in the 
history table.


I need to count the numbers of As, Bs, ... on the end of month.
The subquery x finds the last state before a given date, here february 1st.

select
s.status,
count(*)
from
(
select  distinct on ( object_id )
status_id
from
history
where
ts < '2013/02/01'
order by
object_id,
ts   desc
)   as  x
joinstatus  as  s  on  x.status_id = s.id
group by
s.status
order by
s.status;

Now I need this for a series of months.

This would give me the relevant dates.
select generate_series ( '2013/02/01'::date, current_date + interval '1 
month', interval '1 month' )


How could I combine those 2 queries so that the date in query 1 would be 
replaced dynamically with the result of the series?


To make it utterly perfect the final query should show a crosstab with 
the states as columns.
It is possible that in some months not every state exists so in this 
case the crosstab-cell should show a 0.


Month   AB  C  ...
2013/02/01
2013/03/01
...





--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] Update a composite nested type variable

[Luca Vernini]

I'm writing a system with havy use of composite types.
I have a doubt.

I'm writing all in functions with language plpgsql.
When I read a field from a composite type I must write something like this:
status = ((in_customer.customer_data).customer_status).status_id

And this works fine. I need to enclose the base type, but this is not a
problem.

When I need to assign a value I try to write something like:
(in_customer.customer_data).field_a := NULL;

But postgresql rise an error:
ERROR: syntax error at or near "("
SQL state: 42601

If I dont use parentesis I rise a different error:

ERROR: "in_customer.customer_data.field_a" is not a known variable
SQL state: 42601

2 questions:
Why is the behavior so different in read and in assign.
How can I workaround this and update my values?

Luca.

Re: [SQL] Update a composite nested type variable

[David Johnston]

Luca Vernini wrote
> I'm writing a system with havy use of composite types.
> I have a doubt.
> 
> I'm writing all in functions with language plpgsql.
> When I read a field from a composite type I must write something like
> this:
> status = ((in_customer.customer_data).customer_status).status_id
> 
> And this works fine. I need to enclose the base type, but this is not a
> problem.
> 
> When I need to assign a value I try to write something like:
> (in_customer.customer_data).field_a := NULL;
> 
> But postgresql rise an error:
> ERROR: syntax error at or near "("
> SQL state: 42601
> 
> If I dont use parentesis I rise a different error:
> 
> ERROR: "in_customer.customer_data.field_a" is not a known variable
> SQL state: 42601
> 
> 2 questions:
> Why is the behavior so different in read and in assign.
> How can I workaround this and update my values?
> 
> Luca.

This may be a pl/pgsql limitation but you should probably provide a complete
self-contained example with your attempt so that user-error can be
eliminated.

David J.





--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Update-a-composite-nested-type-variable-tp5763023p5763082.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Update a composite nested type variable

[Luca Vernini]

2013/7/8 David Johnston 

>
> This may be a pl/pgsql limitation but you should probably provide a
> complete
> self-contained example with your attempt so that user-error can be
> eliminated.
>
> David J.
>
>
All right. Here you are a complete example. Just tested it.
Sorry for the long email.

 CREATE TYPE type_customer AS
   (id integer,
cust_name character varying(100),
email character varying(100));

CREATE TYPE type_supercustomer AS
   (cus_data type_customer,
superpower character varying);

CREATE TABLE public.table_customer
(
   id serial NOT NULL,
   cust_name character varying(100) NOT NULL,
   email character varying(100) NOT NULL,
   PRIMARY KEY (id)
)
WITH (OIDS = FALSE);

CREATE TABLE public.table_supercustomer
(
   superpower character varying(100) NOT NULL
)
INHERITS (table_customer)
WITH (OIDS = FALSE);
ALTER TABLE table_supercustomer ADD PRIMARY KEY (id);

CREATE OR REPLACE FUNCTION function_read_supercustomer()
  RETURNS SETOF type_supercustomer AS
$BODY$
DECLARE
retsettype_supercustomer;
BEGIN
FOR retset IN
SELECT (id, cust_name, email), superpower
FROM table_supercustomer
LOOP
retset.superpower := initcap(retset.superpower);--works
(retset.cus_data).email = 'anything you want';--does not work
RETURN NEXT retset;
END LOOP;
END;
$BODY$
  LANGUAGE plpgsql STABLE STRICT COST 100 ROWS 1000;

Re: [SQL] Update a composite nested type variable

[David Johnston]

Luca Vernini wrote
> 2013/7/8 David Johnston <

> polobo@

> >
> 
>>
>> This may be a pl/pgsql limitation but you should probably provide a
>> complete
>> self-contained example with your attempt so that user-error can be
>> eliminated.
>>
>> David J.
>>
>>
> All right. Here you are a complete example. Just tested it.
> Sorry for the long email.

This does appear to be a limitation.  The documentation says pl/pgsql allows
for "simple variables" in the target which 2-layer composite types do not
qualify for.

As a work-around I'd suggest creating local variables for each of the
relevant fields - say by using the same names but with "_" instead of "."; 
You will then need to reconstruct each complex value from the basic values
and return the reconstructed value.

r_cus_id := retset.cus_data.id;
r_cus_name := retset.cus_data.name;
r_cus_email := retset.cus_data.email;
r_superpower := retset.superpower:

RETURN SELECT (r_cus_id, r_cus_name, r_cus_email)::type_customer,
r_superpower)::type_supercustomer;

Not tested but as I am writing this I am getting a Deja-Vu sensation which I
think means I am correct and that this somewhat convoluted way is what
you've got.

David J.




--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Update-a-composite-nested-type-variable-tp5763023p5763119.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql