Example of using window functions with

VOPS:

separator (default is ',') and number of lines in CSV header (no header

by default). The function returns number of imported rows.

A query from VOPS projection returns set of tiles. Output function of

tile type is able to print content of the tile. But in some cases it is

preferable to transfer result to normal (horizontal) format where each

tuple represents one record. It can be done using`unnest`

tuple represents one record. It can be done using`vops_unnest`

function:

As it was mentioned in previous section,`unnest` function can scatter

records with VOPS types into normal records with scalar types. So it is

possible to use this records in arbitrary SQL queries. But there are two

problems withunnest function:

As it was mentioned in previous section,`vops_unnest` function can

scatterrecords with VOPS types into normal records with scalar types.

So it ispossible to use this records in arbitrary SQL queries. But

there are twoproblems withvops_unnest function:

1. It is not convenient to use. This function has no static knowledge

about the format of output record and this is why programmer has to

specify it manually, if here wants to decompose this record.

2. PostgreSQL optimizer has completely no knowledge on result of

transformation performed byunnest() function. This is why it is not

able to choose optimal query execution plan for data retrieved from

VOPS table.

transformation performed byvops_unnest() function. This is why it

is notable to choose optimal query execution plan for data

retrieved fromVOPS table.

Fortunately Postgres provides solution for both of this problem: foreign

data wrappers (FDW). In our case data is not really "foreign": it is

6

(1 row)

selectunnest(v.*) from v where x > 1;

--------

selectvops_unnest(v.*) from v where x > 1;

-------------

(2)

(3)

(4)

6

(1 row)

selectunnest(t.*) from (select mcount(*) over w,mcount(x) over w,msum(x) over w,mavg(x) over w,mmin(x) over w,mmax(x) over w,x - lag(x) over w from v window w as (rows between unbounded preceding and current row)) t;

selectvops_unnest(t.*) from (select mcount(*) over w,mcount(x) over w,msum(x) over w,mavg(x) over w,mmin(x) over w,mmax(x) over w,x - lag(x) over w from v window w as (rows between unbounded preceding and current row)) t;

-------------------

(1,1,1,1,1,1,)

(2,2,3,1.5,1,2,1)

100

(1 row)

selectunnest(t.*) from (select msum(x,10) over (order by first(id)) from v2) t;

--------

selectvops_unnest(t.*) from (select msum(x,10) over (order by first(id)) from v2) t;

-------------

(1)

(3)

(6)

createtablev(x vops_float4);

insert into svalues(1.0),(2.0),(null),(3.0),(null),(4.0);

select populate(destination:='v'::regclass, source:='s'::regclass);

selectunnest(v.*)from vwhere x>1;

selectvops_unnest(v.*)from vwhere x>1;

select countall(*)from vwhere xis not null;

selectcount(*)from vwhere x isnull;

selectcount(*)from vwhere xis not null;

selectcount(*),count(x),sum(x),avg(x),min(x),max(x),variance(x),var_pop(x),var_samp(x),stddev(x),stddev_pop(x),stddev_samp(x)from swhere x>1.0;

selectcount(*)from vwhere ifnull(x,0)>=0;

selectcount(*)from vwhere coalesce(x,0.0::float8::vops_float4)>=0;

selectunnest(t.*)from (select mcount(*) over w,mcount(x) over w,msum(x) over w,mavg(x) over w,mmin(x) over w,mmax(x) over w,x- lag(x) over wfrom v window was (rows between unbounded precedingand current row)) t;

selectvops_unnest(t.*)from (select mcount(*) over w,mcount(x) over w,msum(x) over w,mavg(x) over w,mmin(x) over w,mmax(x) over w,x- lag(x) over wfrom v window was (rows between unbounded precedingand current row)) t;

createtables2(x float8, idserial);

insert into s2(select generate_series(1,100));

createtablev2(x vops_float8, id vops_int4);

select populate(destination:='v2'::regclass, source:='s2'::regclass,sort:='id');

selectunnest(t.*)from (select msum(x,10) over (order by first(id))from v2) t;

selectvops_unnest(t.*)from (select msum(x,10) over (order by first(id))from v2) t;

selectsum(x) over (order by id rows between9 precedingand current row)from s2;

setvops.auto_substitute_projections=on;

count(*),

sum(l_extendedprice* (1-l_discount))as revenue

(select c.*from vcustomer vc,unnest(vc.*) c(c_custkey int4,c_nationkey int4,c_acctbalreal)) c1

(select c.*from vcustomer vc,vops_unnest(vc.*) c(c_custkey int4,c_nationkey int4,c_acctbalreal)) c1

(select o.*from vorders vo,unnest(vo.*) o(o_orderkey int4,o_custkey int4,o_orderstatus"char",

(select o.*from vorders vo,vops_unnest(vo.*) o(o_orderkey int4,o_custkey int4,o_orderstatus"char",

o_totalpricereal,o_orderdatedate,o_shippriority int4)

wherevo.o_orderdate>='1996-01-01'::dateandvo.o_orderdate<'1997-01-01'::date) o1

on c_custkey= o_custkey

(select l.*from vlineitem vl,unnest(vl.*) l(l_suppkey int4,l_orderkey int4,l_partkey int4,l_shipdatedate,l_quantity float4,

(select l.*from vlineitem vl,vops_unnest(vl.*) l(l_suppkey int4,l_orderkey int4,l_partkey int4,l_shipdatedate,l_quantity float4,

l_extendedprice float4,l_discount float4,l_tax float4,l_returnflag"char",l_linestatus"char")) l1on l_orderkey= o_orderkey

(select s.*from vsupplier vs,unnest(vs.*) s(s_suppkey int4,s_nationkey int4,s_acctbalreal)) s1on l_suppkey= s_suppkey

(select s.*from vsupplier vs,vops_unnest(vs.*) s(s_suppkey int4,s_nationkey int4,s_acctbalreal)) s1on l_suppkey= s_suppkey

join nationon c_nationkey= n_nationkey

join regionon n_regionkey= r_regionkey

\echo Use"create extension vops" to load this file. \quit

createtypevops_aggregatesas(group_by int8, count int8, aggs float8[]);

createfunctionreduce(bigint) returns setof vops_aggregatesas'MODULE_PATHNAME','vops_reduce' language C parallel safe strict immutable;

createfunctionunnest(anyelement) returns setof recordas'MODULE_PATHNAME','vops_unnest' language C parallel safe strict immutable;

createfunctionvops_unnest(anyelement) returns setof recordas'MODULE_PATHNAME','vops_unnest' language C parallel safe strict immutable;

create cast (vops_boolas bool) with function filter(vops_bool)AS IMPLICIT;

<li><ahref="#window">Vector window functions</a></li>

<li><ahref="#indexes">Using indexes</a></li>

<li><ahref="#populating">Preparing data for VOPS</a></li>

<li><ahref="#unnest">Back to normal tuples</a></li>

<li><ahref="#vops_unnest">Back to normal tuples</a></li>

<li><ahref="#fdw">Back to normal tables</a></li>

<li><ahref="#transform">Standard SQL query transformation</a></li>

Example of using window functions with VOPS:

selectunnest(t.*) from (select mcount(*) over w,mcount(x) over w,msum(x) over w,mavg(x) over w,mmin(x) over w,mmax(x) over w,x - lag(x) over w

selectvops_unnest(t.*) from (select mcount(*) over w,mcount(x) over w,msum(x) over w,mavg(x) over w,mmin(x) over w,mmax(x) over w,x - lag(x) over w

from v window w as (rows between unbounded preceding and current row)) t;

(no header by default). The function returns number of imported rows.

<h3><aname="unnest">Back to normal tuples</a></h3>

<h3><aname="vops_unnest">Back to normal tuples</a></h3>

A query from VOPS projection returns set of tiles. Output function of tile type is able to print content of the tile.

But in some cases it is preferable to transfer result to normal (horizontal) format where each tuple represents one record.

It can be done using<code>unnest</code> function:

It can be done using<code>vops_unnest</code> function:

postgres=# selectunnest(l.*) from vops_lineitem l where filter(l_shipdate &lt;= '1998-12-01'::date) limit 3;

postgres=# selectvops_unnest(l.*) from vops_lineitem l where filter(l_shipdate &lt;= '1998-12-01'::date) limit 3;

---------------------------------------

(1996-03-13,17,33078.9,0.04,0.02,N,O)

(1996-04-12,36,38306.2,0.09,0.06,N,O)

<h3><aname="fdw">Back to normal tables</a></h3>

As it was mentioned in previous section,<code>unnest</code> function can scatter records with VOPS types into normal records with scalar types.

As it was mentioned in previous section,<code>vops_unnest</code> function can scatter records with VOPS types into normal records with scalar types.

So it is possible to use this records in arbitrary SQL queries.

But there are two problems withunnest function:

But there are two problems withvops_unnest function:

<li>It is not convenient to use. This function has no static knowledge about the format of output record and this is why programmer has to specify it manually,

if here wants to decompose this record.</li>

<li>PostgreSQL optimizer has completely no knowledge on result of transformation performed byunnest() function.

<li>PostgreSQL optimizer has completely no knowledge on result of transformation performed byvops_unnest() function.

This is why it is not able to choose optimal query execution plan for data retrieved from VOPS table.</li>

}

appendStringInfoString(&sql," FROM ");

vopsDeparseRelation(&sql,relation);

appendStringInfo(&sql," t,unnest(t) r(%s)",record.data);

appendStringInfo(&sql," t,vops_unnest(t) r(%s)",record.data);

portal=SPI_cursor_open_with_args(NULL,sql.data,0,NULL,NULL,NULL, true,0);