This is the reverse of what I thought I would find.

In short my check constraint is extracting the epoch from a start timestamp,

and an end timestamp to get the number of seconds difference.

It then uses this number to check the array_upper() of an array to make sure

it's the proper size

The SQL version uses a case statement, and the plpgsql uses an IF/ELSE

In a particular insert test

The plpgsql version adds 1 second over the no constraints case.

the sql version adds 10 seconds over the no constraints case.

Why would this be?

--->

CREATE OR REPLACE FUNCTION check_end_time_foo( _start_time TIMESTAMP,

_end_time TIMESTAMP, _granularity SMALLINT, _values DOUBLE PRECISION[] )

RETURNS boolean AS $$

BEGIN

if( _granularity = 5 )

THEN

return( EXTRACT( EPOCH FROM _end_time at time zone 'utc' )::BIGINT -

EXTRACT( EPOCH FROM _start_time at time zone 'utc' )::BIGINT = 60 *

array_upper( _values,1 ) );

ELSEIF( _granularity = 7 )

THEN

return( EXTRACT( EPOCH FROM _end_time at time zone 'utc' )::BIGINT -

EXTRACT( EPOCH FROM _start_time at time zone 'utc' )::BIGINT = 900 *

array_upper( _values,1 ) );

ELSEIF( _granularity = 9 )

THEN

return( EXTRACT( EPOCH FROM _end_time at time zone 'utc' )::BIGINT -

EXTRACT( EPOCH FROM _start_time at time zone 'utc' )::BIGINT = 3600 *

array_upper( _values,1 ) );

ELSEIF( _granularity = 12 )

THEN

return( ( (EXTRACT( YEAR FROM (_end_time at time zone 'utc') at time

zone 'utc' )::INT * 12) + EXTRACT( MONTH FROM (_end_time at time zone 'utc'

) at time zone 'utc' )::INT ) - ( (EXTRACT( YEAR FROM (_start_time at time

zone 'utc') at time zone 'utc' )::INT * 12) + EXTRACT( MONTH FROM

(_start_time at time zone 'utc') at time zone 'utc' )::INT ) = array_upper(

_values,1 ) );

END IF;

END;

$$ language plpgsql IMMUTABLE;

alter table timeseries add CONSTRAINT timeseries_valid_end_time CHECK(

check_end_time_foo( series_start_time, series_end_time, granularity,

data_value ) );

-vs-

alter table timeseries add CONSTRAINT timeseries_valid_end_time CHECK( CASE

WHEN granularity = 5

THEN

EXTRACT( EPOCH FROM series_end_time at time zone 'utc' )::BIGINT - EXTRACT(

EPOCH FROM series_start_time at time zone 'utc' )::BIGINT = 60 *

array_upper( data_value,1 )

WHEN granularity = 7

THEN

EXTRACT( EPOCH FROM series_end_time at time zone 'utc' )::BIGINT - EXTRACT(

EPOCH FROM series_start_time at time zone 'utc' )::BIGINT = 900 *

array_upper( data_value,1 )

WHEN granularity = 9

THEN

EXTRACT( EPOCH FROM series_end_time at time zone 'utc' )::BIGINT - EXTRACT(

EPOCH FROM series_start_time at time zone 'utc' )::BIGINT = 3600 *

array_upper( data_value,1 )

WHEN granularity = 12

THEN

((EXTRACT( YEAR FROM ( series_end_time at time zone 'utc') at time zone

'utc' )::INT * 12) + EXTRACT( MONTH FROM ( series_end_time at time zone

'utc' ) at time zone 'utc' )::INT ) - ( (EXTRACT( YEAR FROM (

series_start_time at time zone 'utc') at time zone 'utc' )::INT * 12) +

EXTRACT( MONTH FROM ( series_start_time at time zone 'utc') at time zone

'utc' )::INT ) = array_upper( data_value,1 )

ELSE

false

END

);