User-defined transform function heat_map must have an OVER clause

[smlcngl]

Hi,

I am getting below error while trying to test the function,

dbadmin=> create table test.heat_maptable (x int, y int);
CREATE TABLE
dbadmin=> INSERT INTO test.heat_maptable values (3,4);

OUTPUT

  1

(1 row)

dbadmin=> select heat_map(x,y) from test.heat_maptable;
ERROR 5401: User-defined transform function heat_map must have an OVER clause

do you have any idea ?

regards,
İsmail

[aseering]

If you look in the "examples/" directory of the project, you'll see a .sql file that includes example queries for each of these functions. You can follow those examples in order to see how to correctly call these functions.

The "OVER" clause defines how the data should be partitioned and ordered. It is part of the standard SQL calling syntax for analytic functions, so you can type "over clause" into your favorite search engine to find documentation and more-detailed examples on how to use it.

With analytic functions, by definition, you get one output set per partition; in this case, that means one heat map per partition. If you want multiple maps, it's typically much more efficient (so the query will run much faster) to run one big partitioned analytic query than it is to run many small queries one for each output that you want. (This applies to all analytic functions; not just heat_map().)

The OVER clause also lets you specify the order of the tuples that are passed into the analytic function. In the case of "heat_map()", this does not have any effect; the order doesn't matter.

If your OVER clause has no arguments, that means no partitions (or, really, one big partition), which is probably the basic case that most people want. It might be useful to include an aggregate-function implementation of heat_map() that can be called in this case, so that the OVER clause is not required. If you're feeling particularly motivated, feel free to contribute one.