CREATE TRUSTED LANGUAGE "plperl" HANDLER "plperl_call_handler";
CREATE TRUSTED LANGUAGE "plpgsql" HANDLER "plpgsql_call_handler" VALIDATOR "plpgsql_validator";


CREATE OR REPLACE FUNCTION next_seq_id() RETURNS trigger AS $$

    my ($col,$seq)=@_;
    my($newval);
    $newval=$_TD->{new}{$col};
    if ($newval==0) {
        $rv=spi_exec_query('SELECT nextval(\''.$seq.'\') as nextval', 1);
        $_TD->{new}{$col}= $rv->{rows}[0]->{nextval};
        return "MODIFY";  # modify row and execute INSERT/UPDATE command
    } else {
        if ($newval>0) {

                $rv=spi_exec_query('SELECT setval(\''.$seq.'\','.($newval).') ', 1);
        }
    }
    return;
$$ LANGUAGE plperl;



CREATE OR REPLACE FUNCTION mdy(m integer, d integer, y integer) RETURNS date AS $$
DECLARE 
 lv_string char(20);
BEGIN
		lv_string=(m::char(2))||' '||(d::char(2))||' '||(y::char(4));
	
                RETURN to_date(lv_string, 'MM DD YYYY');
END;
$$ LANGUAGE plpgsql;







CREATE OR REPLACE FUNCTION char_to_dt_extended (dt text, starttime text, endtime text) RETURNS timestamp AS $$
BEGIN
        return to_timestamp(dt, A4GL_gettimestring(starttime, endtime));
END;
$$
LANGUAGE plpgsql;



CREATE OR REPLACE FUNCTION dt_extend (dt timestamp, starttime text, endtime text) RETURNS timestamp AS $$
DECLARE
        ls integer;
        le integer;
        ts timestamp;
        tc integer; -- count the number of matches
        lm_ls integer; -- last match
        lm_le integer; -- last match

BEGIN
        ls=dt_string_to_int(starttime);
        le=dt_string_to_int(endtime);


        -- lets see if it just 'works'...
        ts=dt;

        if ts is not null then
                return ts;
        end if;

        for ls in 1..6 LOOP
                for le in 1..6 LOOP

                        ts:=to_timestamp(dt, A4GL_gettimestring(ls,le));

                        if ts is not null then
                                tc:=tc+1;
                                lm_ls:=ls;
                                lm_le:=le;
                        end if;

                end loop;
        end loop;

        if le=6 then -- If we've got some seconds we might as well use them all
                le:=11;
        end if;

        if tc=1 then
                return to_timestamp(dt, A4GL_gettimestring(lm_ls,lm_le));
        else
                -- This will be null - because if it were not
                -- it would have already been returned!
                return to_timestamp(dt, A4GL_gettimestring(ls,le));
        end if;
END;
$$

LANGUAGE plpgsql;



CREATE OR REPLACE FUNCTION dt_extend (dt text, starttime text, endtime text) RETURNS timestamp AS $$
DECLARE
        ls integer;
        le integer;
        ts timestamp;
        tc integer; -- count the number of matches
        lm_ls integer; -- last match
        lm_le integer; -- last match

BEGIN
        ls=dt_string_to_int(starttime);
        le=dt_string_to_int(endtime);


        -- lets see if it just 'works'...
        ts=to_timestamp(dt, A4GL_gettimestring(ls,le));

        if ts is not null then
                return ts;
        end if;

        for ls in 1..6 LOOP
                for le in 1..6 LOOP

                        ts:=to_timestamp(dt, A4GL_gettimestring(ls,le));

                        if ts is not null then
                                tc:=tc+1;
                                lm_ls:=ls;
                                lm_le:=le;
                        end if;

                end loop;
        end loop;

        if le=6 then -- If we've got some seconds we might as well use them all
                le:=11;
        end if;

        if tc=1 then
                return to_timestamp(dt, A4GL_gettimestring(lm_ls,lm_le));
        else
                -- This will be null - because if it were not
                -- it would have already been returned!
                return to_timestamp(dt, A4GL_gettimestring(ls,le));
        end if;
END;
$$

LANGUAGE plpgsql;




CREATE OR REPLACE FUNCTION A4GL_gettimestring (p_starttime integer, p_endtime integer) RETURNS text AS $$
 BEGIN
	
	return A4GL_gettimestring(p_starttime::text, p_endtime::text);
 END;
 $$
LANGUAGE plpgsql;



CREATE OR REPLACE FUNCTION A4GL_gettimestring (p_starttime text, p_endtime text) RETURNS text AS $$
 DECLARE
        str text;
        starttime text; endtime text;
 BEGIN
        str:='';
        starttime:=p_starttime;
        endtime:=p_endtime;

        if starttime='1' then starttime:='YEAR'; end if;
        if starttime='2' then starttime:='MONTH'; end if;
        if starttime='3' then starttime:='DAY'; end if;
        if starttime='4' then starttime:='HOUR'; end if;
        if starttime='5' then starttime:='MINUTE'; end if;
        if starttime='6' then starttime:='SECOND'; end if;
        if starttime='7' then starttime:='FRACTION(1)'; end if;
        if starttime='8' then starttime:='FRACTION(2)'; end if;
        if starttime='9' then starttime:='FRACTION(3)'; end if;
        if starttime='10' then starttime:='FRACTION(4)'; end if;
        if starttime='11' then starttime:='FRACTION(5)'; end if;

        if endtime='1' then endtime:='YEAR'; end if;
        if endtime='2' then endtime:='MONTH'; end if;
        if endtime='3' then endtime:='DAY'; end if;
        if endtime='4' then endtime:='HOUR'; end if;
        if endtime='5' then endtime:='MINUTE'; end if;
        if endtime='6' then endtime:='SECOND'; end if;
        if endtime='7' then endtime:='FRACTION(1)'; end if;
        if endtime='8' then endtime:='FRACTION(2)'; end if;
        if endtime='9' then endtime:='FRACTION(3)'; end if;
        if endtime='10' then endtime:='FRACTION(4)'; end if;
        if endtime='11' then endtime:='FRACTION(5)'; end if;



        if starttime='YEAR' and endtime='YEAR' then
                str:='YYYY';
        end if;
        if starttime='YEAR' and endtime='MONTH' then
                str:='YYYY-MM';
        end if;
        if starttime='YEAR' and endtime='DAY' then
                str:='YYYY-MM-DD';
        end if;
        if starttime='YEAR' and endtime='HOUR' then
                str:='YYYY-MM-DD HH24';
        end if;
        if starttime='YEAR' and endtime='MINUTE' then
                str:='YYYY-MM-DD HH24:MI';
        end if;
        if starttime='YEAR' and endtime='SECOND' then
                str:='YYYY-MM-DD HH24:MI:SS';
        end if;
        if starttime='YEAR' and ( endtime='FRACTION' OR endtime='FRACTION(1)' OR endtime='FRACTION(2)' OR endtime='FRACTION(3)' OR endtime='FRACTION(4)' OR endtime='FRACTION(5)' ) then
                str:='YYYY-MM-DD HH24:MI:SS.MS';
        end if;

        if starttime='MONTH' and endtime='MONTH' then
                str:='MM';
        end if;
        if starttime='MONTH' and endtime='DAY' then
                str:='MM-DD';
        end if;
        if starttime='MONTH' and endtime='HOUR' then
                str:='MM-DD HH24';
        end if;
        if starttime='MONTH' and endtime='MINUTE' then
                str:='MM-DD HH24:MI';
        end if;
        if starttime='MONTH' and endtime='SECOND' then
                str:='MM-DD HH24:MI:SS';
        end if;
        if starttime='MONTH' and ( endtime='FRACTION' OR endtime='FRACTION(1)' OR endtime='FRACTION(2)' OR endtime='FRACTION(3)' OR endtime='FRACTION(4)' OR endtime='FRACTION(5)' ) then
                str:='MM-DD HH24:MI:SS.MS';
        end if;

        if starttime='DAY' and endtime='DAY' then
                str:='DD';
        end if;
        if starttime='DAY' and endtime='HOUR' then
                str:='DD HH24';
        end if;
        if starttime='DAY' and endtime='MINUTE' then
                str:='DD HH24:MI';
        end if;
        if starttime='DAY' and endtime='SECOND' then
                str:='DD HH24:MI:SS';
        end if;
        if starttime='DAY' and ( endtime='FRACTION' OR endtime='FRACTION(1)' OR endtime='FRACTION(2)' OR endtime='FRACTION(3)' OR endtime='FRACTION(4)' OR endtime='FRACTION(5)' ) then
                str:='DD HH24:MI:SS.MS';
        end if;

        if starttime='HOUR' and endtime='HOUR' then
                str:='HH24';
        end if;
        if starttime='HOUR' and endtime='MINUTE' then
                str:='HH24:MI';
        end if;
        if starttime='HOUR' and endtime='SECOND' then
                str:='HH24:MI:SS';
        end if;
        if starttime='HOUR' and ( endtime='FRACTION' OR endtime='FRACTION(1)' OR endtime='FRACTION(2)' OR endtime='FRACTION(3)' OR endtime='FRACTION(4)' OR endtime='FRACTION(5)' ) then
                str:='HH24:MI:SS.MS';
        end if;

        if starttime='MINUTE' and endtime='MINUTE' then
                str:='MI';
        end if;
        if starttime='MINUTE' and endtime='SECOND' then
                str:='MI:SS';
        end if;
        if starttime='MINUTE' and ( endtime='FRACTION' OR endtime='FRACTION(1)' OR endtime='FRACTION(2)' OR endtime='FRACTION(3)' OR endtime='FRACTION(4)' OR endtime='FRACTION(5)' ) then
                str:='MI:SS.MS';
        end if;

        if starttime='SECOND' and endtime='SECOND' then
                str:='SS';
        end if;
        if starttime='SECOND' and ( endtime='FRACTION' OR endtime='FRACTION(1)' OR endtime='FRACTION(2)' OR endtime='FRACTION(3)' OR endtime='FRACTION(4)' OR endtime='FRACTION(5)' ) then
                str:='SS.MS';
        end if;

return str;
 END;
 $$

LANGUAGE plpgsql;



CREATE OR REPLACE FUNCTION iv_extend (iv text, starttime text, i integer, endtime text) RETURNS text AS $$
 BEGIN
        return iv;
 END;
 $$

LANGUAGE plpgsql;



CREATE OR REPLACE FUNCTION char_to_iv_extended (iv text, starttime text, endtime text) RETURNS text AS $$
 BEGIN
        return iv;
 END;
 $$

LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION dt_string_to_int (starttime text) RETURNS integer AS $$
BEGIN
        if starttime='YEAR'     THEN return 1; end if;
        if starttime='MONTH'    THEN return 2; end if;
        if starttime='DAY'      THEN return 3; end if;
        if starttime='HOUR'     THEN return 4; end if;
        if starttime='MINUTE'   THEN return 5; end if;
        if starttime='SECOND'   THEN return 6; end if;

        if starttime='FRACTION(1)'      THEN return 7; end if;
        if starttime='FRACTION'         THEN return 8; end if;
        if starttime='FRACTION(2)'      THEN return 8; end if;
        if starttime='FRACTION(3)'      THEN return 9; end if;
        if starttime='FRACTION(4)'      THEN return 10; end if;
        if starttime='FRACTION(5)'      THEN return 11; end if;
 END;
 $$
LANGUAGE plpgsql;



CREATE OR REPLACE FUNCTION matches_to_regexp (str text,esc text) RETURNS text AS $$
declare
        lv_cnt integer;
        lv_rval text;
        lv_c char;
        lv_contains_dot_start integer;
 BEGIN
        lv_cnt:=0;
        lv_rval:='^';
        lv_contains_dot_start:=0;

-- We should probably be looking at the esc too - but we'll leave that for now...
        for lv_cnt in 1..length(str) loop
                lv_c:=substr(str,lv_cnt,1);
                if lv_c='?' then
                        lv_rval:=lv_rval||'.';
                        lv_contains_dot_start:=1;
                else
                        if lv_c='*' then
                                lv_rval:=lv_rval||'.*';
                                lv_contains_dot_start:=1;
                        else
                                if lv_c='.' then
                                        lv_rval:=lv_rval||'.';
                                else
                                        lv_rval:=lv_rval||lv_c;
                                end if;
                        end if;
                end if;

        end loop;

        if lv_contains_dot_start=1 then
                lv_rval=lv_rval||'[ ]*$';
        end if;

        return lv_rval;

 END;
 $$
LANGUAGE plpgsql;



create view syscolumns as 
select attrelid as tabid,
       attname as colname,
       attnum as colno,
       atttypid as coltype,
       attlen as collength
from pg_attribute;

create view systables as 
select oid as tabid,
       0 as nrows,
      'T'::char(1) as tabtype,
      relnatts as ncols,
      relname as tabname
from pg_class where relhasoids='t' ;

create view sysindexes as 
select oid as tabid,
       0 as nrows,
      'I'::char(1) as idxtype,
      relnatts as ncols,
      relname as idxname
from pg_class where relhasoids!='t' ;

create view sysusers as
select usename as username,
       'D'::char(1) as usertype,
       9::smallint as priority,
       passwd as password
from pg_user;



CREATE FUNCTION pg_catalog.text(integer) RETURNS text STRICT IMMUTABLE LANGUAGE SQL AS 'SELECT textin(int4out($1));';
CREATE CAST (integer AS text) WITH FUNCTION pg_catalog.text(integer) AS IMPLICIT;
CREATE FUNCTION pg_catalog.text(smallint) RETURNS text STRICT IMMUTABLE LANGUAGE SQL AS 'SELECT textin(int2out($1));';
CREATE CAST (smallint AS text) WITH FUNCTION pg_catalog.text(smallint) AS IMPLICIT;
CREATE FUNCTION pg_catalog.text(oid) RETURNS text STRICT IMMUTABLE LANGUAGE SQL AS 'SELECT textin(oidout($1));';
CREATE CAST (oid AS text) WITH FUNCTION pg_catalog.text(oid) AS IMPLICIT;
CREATE FUNCTION pg_catalog.text(date) RETURNS text STRICT IMMUTABLE LANGUAGE SQL AS 'SELECT textin(date_out($1));';
CREATE CAST (date AS text) WITH FUNCTION pg_catalog.text(date) AS IMPLICIT;
CREATE FUNCTION pg_catalog.text(double precision) RETURNS text STRICT IMMUTABLE LANGUAGE SQL AS 'SELECT textin(float8out($1));';
CREATE CAST (double precision AS text) WITH FUNCTION pg_catalog.text(double precision) AS IMPLICIT;
CREATE FUNCTION pg_catalog.text(real) RETURNS text STRICT IMMUTABLE LANGUAGE SQL AS 'SELECT textin(float4out($1));';
CREATE CAST (real AS text) WITH FUNCTION pg_catalog.text(real) AS IMPLICIT;
CREATE FUNCTION pg_catalog.text(time WITH time zone) RETURNS text STRICT IMMUTABLE LANGUAGE SQL AS 'SELECT textin(timetz_out($1));';
CREATE CAST (time WITH time zone AS text) WITH FUNCTION pg_catalog.text(time WITH time zone) AS IMPLICIT;
CREATE FUNCTION pg_catalog.text(time without time zone) RETURNS text STRICT IMMUTABLE LANGUAGE SQL AS 'SELECT textin(time_out($1));';
CREATE CAST (time without time zone AS text) WITH FUNCTION pg_catalog.text(time without time zone) AS IMPLICIT;
CREATE FUNCTION pg_catalog.text(timestamp WITH time zone) RETURNS text STRICT IMMUTABLE LANGUAGE SQL AS 'SELECT textin(timestamptz_out($1));';
CREATE CAST (timestamp WITH time zone AS text) WITH FUNCTION pg_catalog.text(timestamp WITH time zone) AS IMPLICIT;
CREATE FUNCTION pg_catalog.text(interval) RETURNS text STRICT IMMUTABLE LANGUAGE SQL AS 'SELECT textin(interval_out($1));';
CREATE CAST (interval AS text) WITH FUNCTION pg_catalog.text(interval) AS IMPLICIT;
CREATE FUNCTION pg_catalog.text(bigint) RETURNS text STRICT IMMUTABLE LANGUAGE SQL AS 'SELECT textin(int8out($1));';
CREATE CAST (bigint AS text) WITH FUNCTION pg_catalog.text(bigint) AS IMPLICIT;
CREATE FUNCTION pg_catalog.text(numeric) RETURNS text STRICT IMMUTABLE LANGUAGE SQL AS 'SELECT textin(numeric_out($1));';
CREATE CAST (numeric AS text) WITH FUNCTION pg_catalog.text(numeric) AS IMPLICIT;
CREATE FUNCTION pg_catalog.text(timestamp without time zone) RETURNS text STRICT IMMUTABLE LANGUAGE SQL AS 'SELECT textin(timestamp_out($1));';
CREATE CAST (timestamp without time zone AS text) WITH FUNCTION pg_catalog.text(timestamp without time zone) AS IMPLICIT;