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.klaver@aklaver.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.klaver@aklaver.com