--
-- PostgreSQL database dump
--

SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

--
-- Name: plpythonu_bug; Type: SCHEMA; Schema: -; Owner: -
--

CREATE SCHEMA plpythonu_bug;


SET search_path = plpythonu_bug, pg_catalog;

--
-- Name: timerange; Type: TYPE; Schema: plpythonu_bug; Owner: -
--

CREATE TYPE timerange AS (
	begin_ts timestamp without time zone,
	end_ts timestamp without time zone
);


--
-- Name: gaps(timestamp without time zone, timestamp without time zone, interval); Type: FUNCTION; Schema: plpythonu_bug; Owner: -
--

CREATE FUNCTION gaps(start_ts timestamp without time zone, end_ts timestamp without time zone, gap_length interval) RETURNS SETOF timerange
    LANGUAGE plpgsql
    AS $$

DECLARE
  prev timestamp;
  curr timestamp;
  tr   timerange;
BEGIN
  FOR curr IN SELECT ts FROM timeseries
                        WHERE ts BETWEEN start_ts AND end_ts
                        ORDER BY ts
  LOOP
     IF curr - prev > gap_length THEN
         tr.begin_ts := prev;
         tr.end_ts := curr;
         RETURN NEXT tr;
     END IF;
     prev := curr;
  END LOOP;
  RETURN;
END;
$$;


--
-- Name: pygaps(timestamp without time zone, timestamp without time zone, interval); Type: FUNCTION; Schema: plpythonu_bug; Owner: -
--

CREATE FUNCTION pygaps(start_ts timestamp without time zone, end_ts timestamp without time zone, gap_length interval) RETURNS SETOF timerange
    LANGUAGE plpythonu
    AS $$

    # because pg passes date/time to python as strings I'm using pg to
    # recompute values as seconds so I have numbers to do math

    gap = plpy.execute("select extract(epoch from '%s'::interval) as sec"
                       % gap_length)[0]['sec']

    results = plpy.execute("""select ts, extract(epoch from ts) as epoch
                              from timeseries
                              where ts between '%s' and '%s'"""
                           % (start_ts, end_ts))
    if results.nrows() < 2:
        return

    # prime the well by setting prev(ious) to the first tic and
    # iterate starting with the second...
    prev = results[0]
    for curr in results[1:]:
        # yield timestamp pairs for gaps of timestamps greater than gap
        if curr['epoch'] - prev['epoch'] > gap:
            yield dict(begin_ts=prev['ts'], end_ts=curr['ts'])

        prev = curr

    return

$$;


SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: timeseries; Type: TABLE; Schema: plpythonu_bug; Owner: -; Tablespace: 
--

CREATE TABLE timeseries (
    ts timestamp without time zone
);


--
-- PostgreSQL database dump complete
--