3 | 3 | 9 | 3 | 2 | 4 | 1 | 0.666666666666667 | 1 | 1 | 0.816496580927726 | 1

(1 row)

select count(*) from v where ifnull(x, 0) >= 0;

count

-------

64

(1 row)

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>=0.0;

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 vwhere x>1.0;

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;

createfunctionbetwixt(opd vops_char, low int4, high int4) returns vops_boolas'MODULE_PATHNAME','vops_betwixt_char' language C parallel safe immutable strict;

createfunctionifnull(opd vops_char, subst int4) returns vops_charas'MODULE_PATHNAME','vops_ifnull_char' language C parallel safe immutable;

createfunctionvops_char_neg(right vops_char) returns vops_charas'MODULE_PATHNAME' language C parallel safe immutable strict;

create operator- (rightarg=vops_char, procedure=vops_char_neg);

createfunctionbetwixt(opd vops_int2, low int4, high int4) returns vops_boolas'MODULE_PATHNAME','vops_betwixt_int2' language C parallel safe immutable strict;

createfunctionifnull(opd vops_int2, subst int4) returns vops_int2as'MODULE_PATHNAME','vops_ifnull_int2' language C parallel safe immutable;

createfunctionvops_int2_neg(right vops_int2) returns vops_int2as'MODULE_PATHNAME' language C parallel safe immutable strict;

create operator- (rightarg=vops_int2, procedure=vops_int2_neg);

createfunctionbetwixt(opd vops_int4, low int4, high int4) returns vops_boolas'MODULE_PATHNAME','vops_betwixt_int4' language C parallel safe immutable strict;

createfunctionifnull(opd vops_int4, subst int4) returns vops_int4as'MODULE_PATHNAME','vops_ifnull_int4' language C parallel safe immutable;

createfunctionvops_int4_neg(right vops_int4) returns vops_int4as'MODULE_PATHNAME' language C parallel safe immutable strict;

create operator- (rightarg=vops_int4, procedure=vops_int4_neg);

createfunctionbetwixt(opd vops_date, lowdate, highdate) returns vops_boolas'MODULE_PATHNAME','vops_betwixt_int4' language C parallel safe immutable strict;

createfunctionifnull(opd vops_date, substdate) returns vops_dateas'MODULE_PATHNAME','vops_ifnull_int4' language C parallel safe immutable;

createfunctionvops_date_neg(right vops_date) returns vops_dateas'MODULE_PATHNAME','vops_int4_neg' language C parallel safe immutable strict;

create operator- (rightarg=vops_date, procedure=vops_date_neg);

createfunctionbetwixt(opd vops_timestamp, lowtimestamp, hightimestamp) returns vops_boolas'MODULE_PATHNAME','vops_betwixt_int8' language C parallel safe immutable strict;

createfunctionifnull(opd vops_timestamp, substtimestamp) returns vops_timestampas'MODULE_PATHNAME','vops_ifnull_int8' language C parallel safe immutable;

createfunctionvops_timestamp_neg(right vops_timestamp) returns vops_timestampas'MODULE_PATHNAME','vops_int8_neg' language C parallel safe immutable strict;

create operator- (rightarg=vops_timestamp, procedure=vops_timestamp_neg);

createfunctionbetwixt(opd vops_int8, low int8, high int8) returns vops_boolas'MODULE_PATHNAME','vops_betwixt_int8' language C parallel safe immutable strict;

createfunctionifnull(opd vops_int8, subst int8) returns vops_int8as'MODULE_PATHNAME','vops_ifnull_int8' language C parallel safe immutable;

createfunctionvops_int8_neg(right vops_int8) returns vops_int8as'MODULE_PATHNAME' language C parallel safe immutable strict;

create operator- (rightarg=vops_int8, procedure=vops_int8_neg);

createfunctionbetwixt(opd vops_float4, low float8, high float8) returns vops_boolas'MODULE_PATHNAME','vops_betwixt_float4' language C parallel safe immutable strict;

createfunctionifnull(opd vops_float4, subst float8) returns vops_float4as'MODULE_PATHNAME','vops_ifnull_float4' language C parallel safe immutable;

createfunctionvops_float4_neg(right vops_float4) returns vops_float4as'MODULE_PATHNAME' language C parallel safe immutable strict;

create operator- (rightarg=vops_float4, procedure=vops_float4_neg);

createfunctionbetwixt(opd vops_float8, low float8, high float8) returns vops_boolas'MODULE_PATHNAME','vops_betwixt_float8' language C parallel safe immutable strict;

createfunctionifnull(opd vops_float8, subst float8) returns vops_float8as'MODULE_PATHNAME','vops_ifnull_float8' language C parallel safe immutable;

createfunctionvops_float8_neg(right vops_float8) returns vops_float8as'MODULE_PATHNAME' language C parallel safe immutable strict;

create operator- (rightarg=vops_float8, procedure=vops_float8_neg);

createfunctionvops_initialize() returns voidas'MODULE_PATHNAME' language C;

createfunctionfilter(condition vops_bool) returns boolas'MODULE_PATHNAME','vops_filter' language C parallel safe strict immutable;

createfunctionpopulate(destination regclass, source regclass, predicate cstring defaultnull, sort cstring defaultnull) returns voidas'MODULE_PATHNAME','vops_populate' language C;

createfunctionis_null(anyelement) returns vops_boolas'MODULE_PATHNAME','vops_is_null' language C parallel safe immutable;

createfunctionis_not_null(anyelement) returns vops_boolas'MODULE_PATHNAME','vops_is_not_null' language C parallel safe immutable;

PG_RETURN_POINTER(result);\

}\

#defineIFNULL_OP(TYPE,CTYPE,GXTYPE)\

PG_FUNCTION_INFO_V1(vops_ifnull_##TYPE);\

Datum vops_ifnull_##TYPE(PG_FUNCTION_ARGS)\

{\

vops_##TYPE* opd = (vops_##TYPE*)PG_GETARG_POINTER(0);\

CTYPE subst = (CTYPE)PG_GETARG_##GXTYPE(1);\

vops_##TYPE* result = (vops_##TYPE*)palloc(sizeof(vops_##TYPE)); \

int i;\

for (i = 0; i < TILE_SIZE; i++) {\

result->payload[i] = (opd->nullmask & ((uint64)1 << i)) ? subst : opd->payload[i]; \

}\

result->nullmask = 0; \

PG_RETURN_POINTER(result);\

}\

#defineBIN_RCONST_OP(TYPE,XTYPE,GXTYPE,OP,COP)\

PG_FUNCTION_INFO_V1(vops_##TYPE##_##OP##_rconst); \

Datum vops_##TYPE##_##OP##_rconst(PG_FUNCTION_ARGS)\

CMP_LCONST_OP(TYPE,XTYPE,GXTYPE,ge,>=)\

CMP_RCONST_OP(TYPE,XTYPE,GXTYPE,ge,>=)\

BETWIXT_OP(TYPE,XTYPE,GXTYPE)\

IFNULL_OP(TYPE,CTYPE,GXTYPE)\

COUNT_AGG(TYPE)\

SUM_AGG(TYPE,STYPE,GSTYPE)\

AVG_AGG(TYPE)\

}

PG_RETURN_POINTER(result);

}

PG_FUNCTION_INFO_V1(vops_initialize);

Datumvops_initialize(PG_FUNCTION_ARGS)

{

PG_RETURN_VOID();

}

staticOidvops_bool_oid;

staticOidfilter_oid;

<tr><td><code>&</code></td><td>Boolean AND</td></tr>

<tr><td><code>|</code></td><td>Boolean OR</td></tr>

<tr><td><code>!</code></td><td>Boolean NOT</td></tr>

<tr><td><code>BITWIXT</code></td><td>Analog of BETWEEN</td></tr>

<tr><td><code>bitwixt(x,low,high)</code></td><td>Analog of BETWEEN</td></tr>

<tr><td><code>is_null(x)</code></td><td>Analog of IS NULL</td></tr>

<tr><td><code>is_not_null(x)</code></td><td>Analog of IS NOT NULL</td></tr>

<tr><td><code>ifnull(x,subst)</code></td><td>Analog of COALESCE</td></tr>

<h3><aname="aggregates">Vector aggregates</a></h3>

But if extension was not registered in<code>shared_preload_libraries</code> list, then it will be loaded on demand when any function of this extension is requested.

Unfortunately it happens<b>after</b> parse analyze is done. So first time you execute VOPS query, it will not be transformed. You can get wrong result in this case.

Either take it in account, either add<code>vops</code> to<code>shared_preload_libraries</code> configuration string.

VOPS extension provides special function<code>vops_initialize()</code> which can be invoked to force initialization of VOPS extension.

After invocation of this function, extension will be loaded and all subsequent queries will be normally transformed and produce expected results.

<h2><aname="example">Example</a></h2>