src/test/regress/expected/window_views.out

-- Test deparsing of window functions
set client_min_messages = 'WARNING';
drop schema if exists window_views  cascade;
create schema window_views;
set search_path=window_views;
CREATE TABLE testtab (a int4, b int4);
reset client_min_messages;
create view v1 as
  select avg(a) over (partition by b)
  from testtab;
select pg_get_viewdef('v1');
                       pg_get_viewdef                        
-------------------------------------------------------------
  SELECT avg(testtab.a) OVER (PARTITION BY testtab.b) AS avg+
    FROM testtab;
(1 row)

create view v2 as
  select avg(a) over (order by b)
  from testtab;
select pg_get_viewdef('v2');
                     pg_get_viewdef                      
---------------------------------------------------------
  SELECT avg(testtab.a) OVER (ORDER BY testtab.b) AS avg+
    FROM testtab;
(1 row)

create view v3 as
  select avg(a) over (partition by a order by b)
  from testtab;
select pg_get_viewdef('v3');
                                 pg_get_viewdef                                 
--------------------------------------------------------------------------------
  SELECT avg(testtab.a) OVER (PARTITION BY testtab.a ORDER BY testtab.b) AS avg+
    FROM testtab;
(1 row)

create view v4 as
  select avg(a) over (w)
  from testtab window w as ();
select pg_get_viewdef('v4');
            pg_get_viewdef            
--------------------------------------
  SELECT avg(testtab.a) OVER w AS avg+
    FROM testtab                     +
   WINDOW w AS ();
(1 row)

create view v5 as
  select avg(a) over (w order by b)
  from testtab window w as (partition by a);
select pg_get_viewdef('v5');
                      pg_get_viewdef                       
-----------------------------------------------------------
  SELECT avg(testtab.a) OVER (w ORDER BY testtab.b) AS avg+
    FROM testtab                                          +
   WINDOW w AS (PARTITION BY testtab.a);
(1 row)

create view v6 as
  select avg(a) over (w order by a)
  from testtab window w as ();
select pg_get_viewdef('v6');
                      pg_get_viewdef                       
-----------------------------------------------------------
  SELECT avg(testtab.a) OVER (w ORDER BY testtab.a) AS avg+
    FROM testtab                                          +
   WINDOW w AS ();
(1 row)

create view v7 as
  select avg(a) over (w)
  from testtab window w as (order by a);
select pg_get_viewdef('v7');
            pg_get_viewdef            
--------------------------------------
  SELECT avg(testtab.a) OVER w AS avg+
    FROM testtab                     +
   WINDOW w AS (ORDER BY testtab.a);
(1 row)

create view v8 as
  select avg(a) over (w)
  from testtab window w as (order by a rows between unbounded preceding and unbounded following);
select pg_get_viewdef('v8');
                                        pg_get_viewdef                                        
----------------------------------------------------------------------------------------------
  SELECT avg(testtab.a) OVER w AS avg                                                        +
    FROM testtab                                                                             +
   WINDOW w AS (ORDER BY testtab.a ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING);
(1 row)

create view v9 as
  select avg(a) over (w)
  from testtab window w as (order by a rows between 1 preceding and 1 following);
select pg_get_viewdef('v9');
                                pg_get_viewdef                                
------------------------------------------------------------------------------
  SELECT avg(testtab.a) OVER w AS avg                                        +
    FROM testtab                                                             +
   WINDOW w AS (ORDER BY testtab.a ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING);
(1 row)

create view v10 as
  select avg(a) over (w rows between 1 preceding and 1 following)
  from testtab window w as (order by a);
select pg_get_viewdef('v10');
                                 pg_get_viewdef                                  
---------------------------------------------------------------------------------
  SELECT avg(testtab.a) OVER (w ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS avg+
    FROM testtab                                                                +
   WINDOW w AS (ORDER BY testtab.a);
(1 row)

-- Check that COUNT(*) is dumped with the star intact.
create view v_star as
  select count(*) over (partition by a)
  from testtab;
select pg_get_viewdef('v_star');
                     pg_get_viewdef                      
---------------------------------------------------------
  SELECT count(*) OVER (PARTITION BY testtab.a) AS count+
    FROM testtab;
(1 row)