4 ms·

Limitations like this are among the most trivial of the reasons I pay for MS SQL. Even though no such built in aggregate is available, I'm free to write my own

by kogir 17y ago

Limitations like this are among the most trivial of the reasons I pay for MS SQL. Even though no such built in aggregate is available, I'm free to write my own in C/C++/.Net. I'm pretty sure PostgreSQL lets you extend it however you want as well.

joshu 17y ago
You can write plugins in mysql as well. http://dev.mysql.com/doc/refman/5.1/en/plugin-creating.html http://dev.mysql.com/doc/refman/5.1/en/plugin-creating.html Please do not make things up and claim your imagination as truth.
- kogir 17y ago
  My bad. I suppose you could also credibly argue that since it's open source nothing is impossible, and that MySQL is infinitely more extensible than MSSQL. Also, it seems C/C++ support is deprecated in MSSQL, so it's not a proper solution either. CLR integration is what they're pushing now.
- nimbix 17y ago
  The Postgres way is much simpler: CREATE OR REPLACE FUNCTION product_sfunc(state NUMERIC, value NUMERIC) RETURNS NUMERIC AS $$ BEGIN IF value IS NOT NULL THEN IF state IS NULL THEN RETURN value; ELSE RETURN state * value; END IF; END IF; RETURN state; END; $$ LANGUAGE plpgsql; CREATE AGGREGATE PRODUCT (NUMERIC) ( SFUNC = product_sfunc, STYPE = NUMERIC ); This took only a couple of minutes to write and didn't require any extra tools. Now I can use the PRODUCT() aggregate in queries, like this: SELECT SUM(n), PRODUCT(n) from generate_series(1, 5) as n; sum | product -----+--------- 15 | 120 In Postgres 8.4 it also works as a windowing function which means I can use it to get a running product (is that the correct expression?): SELECT n, PRODUCT(n) OVER (ORDER BY n) from generate_series(1, 5) as n; n | product ---+--------- 1 | 1 2 | 2 3 | 6 4 | 24 5 | 120
  - joshu 17y ago
    You presented some made up limitation as fact; you said it CANNOT be extended. Now you're trying to support your argument further by saying it's EASIER elsewhere. You should have just said that. I hate it when the opinionated use made up facts to justify their opinions. Please don't do that. All these database engines are pretty weak. I prefer to use SAS for this kind of thing (including seeing stuff from previous/future rows.) It may not be client/server, but it is astoundingly fast for large datasets nonetheless.
    - kogir 17y ago
      The person to whom you replied did no such thing. For someone so caught up in correctness, it's surprising you can't be bothered to read user names :)

by kogir 17y ago

joshu 17y ago

You can write plugins in mysql as well. http://dev.mysql.com/doc/refman/5.1/en/plugin-creating.html http://dev.mysql.com/doc/refman/5.1/en/plugin-creating.html Please do not make things up and claim your imagination as truth.

kogir 17y ago
My bad. I suppose you could also credibly argue that since it's open source nothing is impossible, and that MySQL is infinitely more extensible than MSSQL. Also, it seems C/C++ support is deprecated in MSSQL, so it's not a proper solution either. CLR integration is what they're pushing now.
nimbix 17y ago
The Postgres way is much simpler: CREATE OR REPLACE FUNCTION product_sfunc(state NUMERIC, value NUMERIC) RETURNS NUMERIC AS $$ BEGIN IF value IS NOT NULL THEN IF state IS NULL THEN RETURN value; ELSE RETURN state * value; END IF; END IF; RETURN state; END; $$ LANGUAGE plpgsql; CREATE AGGREGATE PRODUCT (NUMERIC) ( SFUNC = product_sfunc, STYPE = NUMERIC ); This took only a couple of minutes to write and didn't require any extra tools. Now I can use the PRODUCT() aggregate in queries, like this: SELECT SUM(n), PRODUCT(n) from generate_series(1, 5) as n; sum | product -----+--------- 15 | 120 In Postgres 8.4 it also works as a windowing function which means I can use it to get a running product (is that the correct expression?): SELECT n, PRODUCT(n) OVER (ORDER BY n) from generate_series(1, 5) as n; n | product ---+--------- 1 | 1 2 | 2 3 | 6 4 | 24 5 | 120
- joshu 17y ago
  You presented some made up limitation as fact; you said it CANNOT be extended. Now you're trying to support your argument further by saying it's EASIER elsewhere. You should have just said that. I hate it when the opinionated use made up facts to justify their opinions. Please don't do that. All these database engines are pretty weak. I prefer to use SAS for this kind of thing (including seeing stuff from previous/future rows.) It may not be client/server, but it is astoundingly fast for large datasets nonetheless.
  - kogir 17y ago
    The person to whom you replied did no such thing. For someone so caught up in correctness, it's surprising you can't be bothered to read user names :)