Hello,

I am trying to aggregate some data but conditionally as follows:

SELECT
feed_all_y2012m01.array_accum(message_copies.msg_i d) as
messages_array,
uniq(feed_all_y2012m01.array_accum(obj_mmsi)) as mmsi_array,
CASE WHEN msg_type BETWEEN 1 and 3 THEN
feed_all_y2012m01.array_accum(message_copies.msg_i d) END as msgA,
CASE WHEN msg_type = 18 THEN
feed_all_y2012m01.array_accum(message_copies.msg_i d) END as msgB_std,
CASE WHEN msg_type = 19 THEN
feed_all_y2012m01.array_accum(message_copies.msg_i d) END as msgB_ext,
CASE WHEN obj_type = 'SHIP_TYPE_A' THEN
uniq(feed_all_y2012m01.array_accum(obj_mmsi)) END as mmsi_type_a_array,
CASE WHEN obj_type = 'SHIP_TYPE_B' THEN
uniq(feed_all_y2012m01.array_accum(obj_mmsi)) END as mmsi_type_b_array,
.......

The logic breaks where PGSQL forces me(?) to group by msg_type and
obj_type while I was hoping that msg_type and obj_type could just be
considered as the variables that the conditions would be checked against.
Neither I want to use some form of aggregation on these two fields. Is
there any way that I can achieve something like this?

Thank you
Yiannis

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

Ioannis Anagnostopoulos <ioannis (AT) anatec (DOT) com> writes:
It's not entirely clear what you want to do, but I think it's unlikely
that a query of this form is it. Those array_accum() aggregates will
all compute the same values --- the case expressions only run after
aggregation has finished. So regardless of any grouping issues, this
would not compute what you're wishing for.

I'm thinking maybe you want to do the cases inside the aggregate functions:

feed_all_y2012m01.array_accum(CASE WHEN msg_type BETWEEN 1 and 3 THEN message_copies.msg_id END) as msgA,
feed_all_y2012m01.array_accum(CASE WHEN msg_type = 18 THEN message_copies.msg_id END) as msgB_std,
...

Now, what this is going to feed to array_accum is the msg_id at rows
with the desired msg_type, and NULL at other rows. What you probably
want array_accum to do is ignore the nulls, which it won't do in the
standard incarnation shown in the manual; but you could make a variant
that does ignore nulls by declaring the aggregate transition function
as strict.

regards, tom lane

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

On 08/07/2012 16:24, Tom Lane wrote:
and not in the array_accum. As a result, apart from the fact that it was
not working correctly as you pointed, the msg_type had to be part of the
'group by' ruining my logic. Of course setting it correctly solved all
problems. Now, for the final step of it. You said that SFUNC =
array_append form the example array_accum must be delclared "strict".
May I ask how as I am lost?

Thank again
Yiannis


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

Ioannis Anagnostopoulos <ioannis (AT) anatec (DOT) com> writes:
You would need to create a new SQL function declaration, referencing the
same underlying C code but marked strict. Then make a new aggregate
referencing that as the sfunc instead of the original.

regards, tom lane

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

On 08/07/2012 23:54, Tom Lane wrote:

Regards
Yiannis


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