Re: custom average window function failure

On 10/09/2016 08:01 AM, Sebastian P. Luque wrote:
> On Sun, 9 Oct 2016 06:44:10 -0700,
> Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> wrote:
>
> [...]
>
>> Not sure. When I tried using the above(on 9.5) it failed during the
>> CREATE OR REPLACE FUNCTION public.angle_vectors_avg stage with:
>
>> ERROR: type "vector" does not exist
>
>
>> So where is that coming from in your setup?
>
> Aw nuts, I forgot to include that type definition. Here it is:
>
> CREATE TYPE public.vector AS
> (angle double precision,
> magnitude double precision);
> COMMENT ON TYPE public.vector
> IS 'This type holds the basic descriptors of a vector; namely, angle and magnitude.';
>
>

Hmm:

test=# select version();
version

-----------------------------------------------------------------------------------------------------------------------------
PostgreSQL 9.6.0 on i686-pc-linux-gnu, compiled by gcc (SUSE Linux)
4.8.3 20140627 [gcc-4_8-branch revision 212064], 32-bit
(1 row)

test=# select avg((random(), random())::angle_vectors);
avg
--------------------------------------
(62.4781575734486,0.865270065328572)

test=# select "time" from generate_series('2016-10-08'::timestamp,
'2016-10-10'::timestamp, '5 hours') as t("time")
window w as (partition by date_trunc('day', "time") order by "time");
time
---------------------
2016-10-08 00:00:00
2016-10-08 05:00:00
2016-10-08 10:00:00
2016-10-08 15:00:00
2016-10-08 20:00:00
2016-10-09 01:00:00
2016-10-09 06:00:00
2016-10-09 11:00:00
2016-10-09 16:00:00
2016-10-09 21:00:00
(10 rows)

test=# SELECT "time", avg(random()) over w
from generate_series('2016-10-08'::timestamp, '2016-10-10'::timestamp,
'5 hours') as t("time")
window w as (partition by date_trunc('day', "time") order by "time");
time | avg
---------------------+-------------------
2016-10-08 00:00:00 | 0.387926945462823
2016-10-08 05:00:00 | 0.649316050112247
2016-10-08 10:00:00 | 0.608540423369656
2016-10-08 15:00:00 | 0.561799361603335
2016-10-08 20:00:00 | 0.54945012088865
2016-10-09 01:00:00 | 0.130873893853277
2016-10-09 06:00:00 | 0.443627830361947
2016-10-09 11:00:00 | 0.314536933631947
2016-10-09 16:00:00 | 0.425128075061366
2016-10-09 21:00:00 | 0.385504625830799

test=# SELECT "time", avg((random(), random())::angle_vectors) over w
from generate_series('2016-10-08'::timestamp, '2016-10-10'::timestamp,
'5 hours') as t("time")
window w as (partition by date_trunc('day', "time") order by "time");
ERROR: input data type is not an array

The parts work, the whole does not. At this point I have no idea why.

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com