CREATE OR REPLACE FUNCTION ver_get_revision(
    p_revision        INTEGER, 
    OUT id            INTEGER, 
    OUT revision_time TIMESTAMP,
    OUT start_time    TIMESTAMP,
    OUT schema_change BOOLEAN,
    OUT comment       TEXT,
    OUT user_name     TEXT
) AS $$
    SELECT
        id,
        revision_time,
        start_time,
        schema_change,
        comment,
        user_name
    FROM
        @extschema@.revision
    WHERE
        id = $1
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION ver_get_revisions(p_revisions INTEGER[]) 
RETURNS TABLE(
    id             INTEGER,
    revision_time  TIMESTAMP,
    start_time     TIMESTAMP,
    schema_change  BOOLEAN,
    comment        TEXT,
    user_name      TEXT
) AS $$
    SELECT
        id,
        revision_time,
        start_time,
        schema_change,
        comment,
        user_name
    FROM
        @extschema@.revision
    WHERE
        id = ANY($1)
    ORDER BY
        revision DESC;
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION ver_get_revisions(
    p_start_date TIMESTAMP,
    p_end_date   TIMESTAMP
)
RETURNS TABLE(
    id             INTEGER
) AS $$
    SELECT
        id
    FROM
        @extschema@.revision
    WHERE
        revision_time >= $1 AND
        revision_time <= $2
    ORDER BY
        revision DESC;
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION ver_get_revision(
    p_date_time       TIMESTAMP
) 
RETURNS INTEGER AS 
$$
    SELECT
        id
    FROM
        @extschema@.revision
    WHERE
        id IN (
            SELECT max(id) 
            FROM   @extschema@.revision
            WHERE  revision_time <= $1
        );
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION ver_get_last_revision() 
RETURNS INTEGER AS 
$$
    SELECT
        id
    FROM
        @extschema@.revision
    WHERE
        id IN (
            SELECT max(id) 
            FROM   @extschema@.revision
        );
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION ver_get_table_base_revision(
    p_schema          NAME,
    p_table           NAME
)
RETURNS INTEGER AS
$$
DECLARE
    v_id INTEGER;
BEGIN
    IF NOT @extschema@.ver_is_table_versioned(p_schema, p_table) THEN
        RAISE EXCEPTION 'Table %.% is not versioned', quote_ident(p_schema), quote_ident(p_table);
    END IF;
    
    SELECT
        VER.id
    INTO
        v_id
    FROM
        @extschema@.revision VER
    WHERE
        VER.id IN (
            SELECT min(TBC.revision)
            FROM   @extschema@.versioned_tables VTB,
                   @extschema@.tables_changed TBC
            WHERE  VTB.schema_name = p_schema
            AND    VTB.table_name = p_table
            AND    VTB.id = TBC.table_id
        );
    RETURN v_id;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION ver_get_table_last_revision(
    p_schema          NAME,
    p_table           NAME
) 
RETURNS INTEGER AS
$$
DECLARE
    v_id INTEGER;
BEGIN
    IF NOT @extschema@.ver_is_table_versioned(p_schema, p_table) THEN
        RAISE EXCEPTION 'Table %.% is not versioned', quote_ident(p_schema), quote_ident(p_table);
    END IF;
    
    SELECT
        VER.id
    INTO
        v_id
    FROM
        @extschema@.revision VER
    WHERE
        VER.id IN (
            SELECT max(TBC.revision)
            FROM   @extschema@.versioned_tables VTB,
                   @extschema@.tables_changed TBC
            WHERE  VTB.schema_name = p_schema
            AND    VTB.table_name = p_table
            AND    VTB.id = TBC.table_id
        );
    RETURN v_id;
END;
$$ LANGUAGE plpgsql;