--
-- PostgreSQL database dump
--

SET client_encoding = 'SQL_ASCII';
SET check_function_bodies = false;
SET client_min_messages = warning;

--
-- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: fiasco
--

COMMENT ON SCHEMA public IS 'Standard public schema';


SET search_path = public, pg_catalog;

--
-- Name: plpgsql_call_handler(); Type: FUNCTION; Schema: public; Owner: fiasco
--

CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler
    AS '$libdir/plpgsql', 'plpgsql_call_handler'
    LANGUAGE c;


ALTER FUNCTION public.plpgsql_call_handler() OWNER TO fiasco;

--
-- Name: plpgsql; Type: PROCEDURAL LANGUAGE; Schema: public; Owner: 
--

CREATE TRUSTED PROCEDURAL LANGUAGE plpgsql HANDLER plpgsql_call_handler;


--
-- Name: date_value; Type: TYPE; Schema: public; Owner: guerin
--

CREATE TYPE date_value AS (
	tsvalue double precision,
	tsdate timestamp without time zone
);


ALTER TYPE public.date_value OWNER TO guerin;

--
-- Name: object_composite_row_3; Type: TYPE; Schema: public; Owner: guerin
--

CREATE TYPE object_composite_row_3 AS (
	uniqid bigint,
	memberid bigint,
	membertype character varying(50),
	ownerid smallint,
	tag character varying(50),
	"level" integer
);


ALTER TYPE public.object_composite_row_3 OWNER TO guerin;

--
-- Name: fngetcompositeids2(bigint); Type: FUNCTION; Schema: public; Owner: fiasco
--

CREATE FUNCTION fngetcompositeids2(bigint) RETURNS SETOF object_composite_row_3
    AS $_$

 declare
    r object_composite_row_3%rowtype;
    pid alias for $1;
    x   int;
    qry text;
    retval int;
    rec record;

 begin
    x := 1;

   if not table_exists('tchildren') then
     create temp table tchildren ( 
       uniqid bigint,
       memberid bigint,
       membertype varchar(50),
       ownerid smallint,
       tag varchar(50),
       level int4
     );
   else 
     EXECUTE 'truncate table tchildren;';
   end if;
   
   EXECUTE 'insert into tchildren
             select uniqId,memberId,memberType,ownerid,tag, ' || x || ' as level
             from tblObjectComposite
             where uniqid = ' || pid || '::bigint;';

   qry:= 'select count(*) as numrows from (select * from tchildren where level = ' || x || ' limit 1) as x;'; 
   FOR rec IN EXECUTE qry LOOP
     retVal:= rec.numrows;
   END LOOP;

   while (retVal > 0) loop
       x := x + 1;
       EXECUTE 'insert into tchildren
                 select oc.uniqid,oc.memberid, oc.membertype,oc.ownerid,oc.tag, ' || x  ||
               ' from tblobjectcomposite oc, tchildren tmp 
                  where  oc.uniqid = tmp.memberid and level = (' || (x-1) || ');';

       qry:= 'select count(*) as numrows from (select * from tchildren where level = ' || x || ' limit 1) as x;'; 
       FOR rec IN EXECUTE qry LOOP
         retVal:= rec.numrows;
       END LOOP;
   end loop;

   FOR r IN EXECUTE '
     select uniqId,memberId,memberType,ownerid,tag,level from tchildren order by level' LOOP
     RETURN NEXT r;
   END LOOP;

   return;
 END;

 $_$
    LANGUAGE plpgsql;


ALTER FUNCTION public.fngetcompositeids2(bigint) OWNER TO fiasco;

--
-- Name: fngetcompositeids2_orig(bigint); Type: FUNCTION; Schema: public; Owner: guerin
--

CREATE FUNCTION fngetcompositeids2_orig(bigint) RETURNS SETOF object_composite_row_3
    AS $_$

 declare
    r object_composite_row_3%rowtype;
    pid alias for $1;
    x   int;
    qry text;
    retval int;
    rec record;

 begin
    x := 1;

   create temp table tmp_children ( 
       uniqid bigint,
       memberid bigint,
       membertype varchar(50),
       ownerid smallint,
       tag varchar(50),
       level int4
   );
   
   EXECUTE 'insert into tmp_children 
             select uniqId,memberId,memberType,ownerid,tag, ' || x || ' as level
             from tblObjectComposite
             where uniqid = ' || pid || '::bigint;';

   qry:= 'select count(*) as numrows from (select * from tmp_children where level = ' || x || ' limit 1) as x;'; 
   FOR rec IN EXECUTE qry LOOP
     retVal:= rec.numrows;
   END LOOP;

   while (retVal > 0) loop
       x := x + 1;
       EXECUTE 'insert into tmp_children
                 select oc.uniqid,oc.memberid, oc.membertype,oc.ownerid,oc.tag, ' || x  ||
               ' from tblobjectcomposite oc, tmp_children tmp 
                  where  oc.uniqid = tmp.memberid and level = (' || (x-1) || ');';

       qry:= 'select count(*) as numrows from (select * from tmp_children where level = ' || x || ' limit 1) as x;'; 
       FOR rec IN EXECUTE qry LOOP
         retVal:= rec.numrows;
       END LOOP;
   end loop;

   FOR r IN EXECUTE '
     select uniqId,memberId,memberType,ownerid,tag,level from tmp_children order by level' LOOP
     RETURN NEXT r;
   END LOOP;

   drop table tmp_children; 

   return;
 END;

 
 $_$
    LANGUAGE plpgsql;


ALTER FUNCTION public.fngetcompositeids2_orig(bigint) OWNER TO guerin;

--
-- Name: table_exists(character varying); Type: FUNCTION; Schema: public; Owner: fiasco
--

CREATE FUNCTION table_exists(character varying) RETURNS boolean
    AS $_$

DECLARE
	t_name ALIAS for $1;
	t_result VARCHAR;
BEGIN
	
    if EXISTS (SELECT relname FROM pg_class WHERE relname ~* ('^' || t_name || '$')
		AND relkind = 'r' and pg_table_is_visible(oid)) then
		RETURN TRUE;
    ELSE
		RETURN FALSE;
    END IF;
END;
$_$
    LANGUAGE plpgsql;


ALTER FUNCTION public.table_exists(character varying) OWNER TO fiasco;

--
-- Name: activity; Type: VIEW; Schema: public; Owner: guerin
--

CREATE VIEW activity AS
    SELECT c.relname, l.relation, l."database", l."transaction", l.pid, l."mode", l.granted FROM (pg_locks l JOIN pg_class c ON ((l.relation = c.oid)));


ALTER TABLE public.activity OWNER TO guerin;

SET default_tablespace = '';

SET default_with_oids = true;

--
-- Name: pga_forms; Type: TABLE; Schema: public; Owner: sa; Tablespace: 
--

CREATE TABLE pga_forms (
    formname character varying(64),
    formsource text
);


ALTER TABLE public.pga_forms OWNER TO sa;

--
-- Name: pga_layout; Type: TABLE; Schema: public; Owner: sa; Tablespace: 
--

CREATE TABLE pga_layout (
    tablename character varying(64),
    nrcols smallint,
    colnames text,
    colwidth text
);


ALTER TABLE public.pga_layout OWNER TO sa;

--
-- Name: pga_queries; Type: TABLE; Schema: public; Owner: sa; Tablespace: 
--

CREATE TABLE pga_queries (
    queryname character varying(64),
    querytype character(1),
    querycommand text,
    querytables text,
    querylinks text,
    queryresults text,
    querycomments text
);


ALTER TABLE public.pga_queries OWNER TO sa;

--
-- Name: pga_reports; Type: TABLE; Schema: public; Owner: sa; Tablespace: 
--

CREATE TABLE pga_reports (
    reportname character varying(64),
    reportsource text,
    reportbody text,
    reportprocs text,
    reportoptions text
);


ALTER TABLE public.pga_reports OWNER TO sa;

--
-- Name: pga_schema; Type: TABLE; Schema: public; Owner: sa; Tablespace: 
--

CREATE TABLE pga_schema (
    schemaname character varying(64),
    schematables text,
    schemalinks text
);


ALTER TABLE public.pga_schema OWNER TO sa;

--
-- Name: pga_scripts; Type: TABLE; Schema: public; Owner: sa; Tablespace: 
--

CREATE TABLE pga_scripts (
    scriptname character varying(64),
    scriptsource text
);


ALTER TABLE public.pga_scripts OWNER TO sa;

SET default_with_oids = false;

--
-- Name: tblobjectcomposite; Type: TABLE; Schema: public; Owner: fiasco; Tablespace: 
--

CREATE TABLE tblobjectcomposite (
    uniqid bigint,
    "type" character varying(50),
    memberid bigint,
    tag character varying(50),
    membertype character varying(50),
    ownerid smallint
);


ALTER TABLE public.tblobjectcomposite OWNER TO fiasco;

--
-- Data for Name: pga_forms; Type: TABLE DATA; Schema: public; Owner: sa
--

COPY pga_forms (formname, formsource) FROM stdin;
\.


--
-- Data for Name: pga_layout; Type: TABLE DATA; Schema: public; Owner: sa
--

COPY pga_layout (tablename, nrcols, colnames, colwidth) FROM stdin;
\.


--
-- Data for Name: pga_queries; Type: TABLE DATA; Schema: public; Owner: sa
--

COPY pga_queries (queryname, querytype, querycommand, querytables, querylinks, queryresults, querycomments) FROM stdin;
\.


--
-- Data for Name: pga_reports; Type: TABLE DATA; Schema: public; Owner: sa
--

COPY pga_reports (reportname, reportsource, reportbody, reportprocs, reportoptions) FROM stdin;
\.


--
-- Data for Name: pga_schema; Type: TABLE DATA; Schema: public; Owner: sa
--

COPY pga_schema (schemaname, schematables, schemalinks) FROM stdin;
\.


--
-- Data for Name: pga_scripts; Type: TABLE DATA; Schema: public; Owner: sa
--

COPY pga_scripts (scriptname, scriptsource) FROM stdin;
\.


--
-- Data for Name: tblobjectcomposite; Type: TABLE DATA; Schema: public; Owner: fiasco
--

COPY tblobjectcomposite (uniqid, "type", memberid, tag, membertype, ownerid) FROM stdin;
\.


--
-- Name: composite_memberid; Type: INDEX; Schema: public; Owner: fiasco; Tablespace: 
--

CREATE INDEX composite_memberid ON tblobjectcomposite USING btree (memberid);


ALTER INDEX public.composite_memberid OWNER TO fiasco;

--
-- Name: composite_uniqid; Type: INDEX; Schema: public; Owner: fiasco; Tablespace: 
--

CREATE INDEX composite_uniqid ON tblobjectcomposite USING btree (uniqid);


ALTER INDEX public.composite_uniqid OWNER TO fiasco;

--
-- Name: idx_unique_composite; Type: INDEX; Schema: public; Owner: fiasco; Tablespace: 
--

CREATE UNIQUE INDEX idx_unique_composite ON tblobjectcomposite USING btree (uniqid, tag);


ALTER INDEX public.idx_unique_composite OWNER TO fiasco;

--
-- Name: public; Type: ACL; Schema: -; Owner: fiasco
--

REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM fiasco;
GRANT ALL ON SCHEMA public TO fiasco;
GRANT ALL ON SCHEMA public TO PUBLIC;


--
-- Name: pga_forms; Type: ACL; Schema: public; Owner: sa
--

REVOKE ALL ON TABLE pga_forms FROM PUBLIC;
REVOKE ALL ON TABLE pga_forms FROM sa;
GRANT ALL ON TABLE pga_forms TO sa;
GRANT ALL ON TABLE pga_forms TO PUBLIC;


--
-- Name: pga_layout; Type: ACL; Schema: public; Owner: sa
--

REVOKE ALL ON TABLE pga_layout FROM PUBLIC;
REVOKE ALL ON TABLE pga_layout FROM sa;
GRANT ALL ON TABLE pga_layout TO sa;
GRANT ALL ON TABLE pga_layout TO PUBLIC;


--
-- Name: pga_queries; Type: ACL; Schema: public; Owner: sa
--

REVOKE ALL ON TABLE pga_queries FROM PUBLIC;
REVOKE ALL ON TABLE pga_queries FROM sa;
GRANT ALL ON TABLE pga_queries TO sa;
GRANT ALL ON TABLE pga_queries TO PUBLIC;


--
-- Name: pga_reports; Type: ACL; Schema: public; Owner: sa
--

REVOKE ALL ON TABLE pga_reports FROM PUBLIC;
REVOKE ALL ON TABLE pga_reports FROM sa;
GRANT ALL ON TABLE pga_reports TO sa;
GRANT ALL ON TABLE pga_reports TO PUBLIC;


--
-- Name: pga_schema; Type: ACL; Schema: public; Owner: sa
--

REVOKE ALL ON TABLE pga_schema FROM PUBLIC;
REVOKE ALL ON TABLE pga_schema FROM sa;
GRANT ALL ON TABLE pga_schema TO sa;
GRANT ALL ON TABLE pga_schema TO PUBLIC;


--
-- Name: pga_scripts; Type: ACL; Schema: public; Owner: sa
--

REVOKE ALL ON TABLE pga_scripts FROM PUBLIC;
REVOKE ALL ON TABLE pga_scripts FROM sa;
GRANT ALL ON TABLE pga_scripts TO sa;
GRANT ALL ON TABLE pga_scripts TO PUBLIC;


--
-- Name: tblobjectcomposite; Type: ACL; Schema: public; Owner: fiasco
--

REVOKE ALL ON TABLE tblobjectcomposite FROM PUBLIC;
REVOKE ALL ON TABLE tblobjectcomposite FROM fiasco;
GRANT ALL ON TABLE tblobjectcomposite TO fiasco;
GRANT ALL ON TABLE tblobjectcomposite TO PUBLIC;


--
-- PostgreSQL database dump complete
--