--
-- PostgreSQL database dump
--

-- Started on 2013-12-26 12:31:08

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

--
-- TOC entry 6 (class 2615 OID 36887)
-- Name: teste; Type: SCHEMA; Schema: -; Owner: postgres
--

CREATE SCHEMA teste;


ALTER SCHEMA teste OWNER TO postgres;

--
-- TOC entry 438 (class 2612 OID 16386)
-- Name: plpgsql; Type: PROCEDURAL LANGUAGE; Schema: -; Owner: postgres
--

CREATE PROCEDURAL LANGUAGE plpgsql;


ALTER PROCEDURAL LANGUAGE plpgsql OWNER TO postgres;

SET search_path = public, pg_catalog;

--
-- TOC entry 386 (class 1247 OID 29489)
-- Dependencies: 5
-- Name: dm_codigo; Type: DOMAIN; Schema: public; Owner: postgres
--

CREATE DOMAIN dm_codigo AS double precision;


ALTER DOMAIN public.dm_codigo OWNER TO postgres;

--
-- TOC entry 387 (class 1247 OID 29490)
-- Dependencies: 5
-- Name: dm_data; Type: DOMAIN; Schema: public; Owner: postgres
--

CREATE DOMAIN dm_data AS timestamp without time zone;


ALTER DOMAIN public.dm_data OWNER TO postgres;

--
-- TOC entry 388 (class 1247 OID 29491)
-- Dependencies: 5
-- Name: dm_desccurto; Type: DOMAIN; Schema: public; Owner: postgres
--

CREATE DOMAIN dm_desccurto AS character varying(25);


ALTER DOMAIN public.dm_desccurto OWNER TO postgres;

--
-- TOC entry 389 (class 1247 OID 29492)
-- Dependencies: 5
-- Name: dm_desclongo; Type: DOMAIN; Schema: public; Owner: postgres
--

CREATE DOMAIN dm_desclongo AS character varying(120);


ALTER DOMAIN public.dm_desclongo OWNER TO postgres;

--
-- TOC entry 399 (class 1247 OID 29493)
-- Dependencies: 5
-- Name: dm_descnormal; Type: DOMAIN; Schema: public; Owner: postgres
--

CREATE DOMAIN dm_descnormal AS character varying(60);


ALTER DOMAIN public.dm_descnormal OWNER TO postgres;

--
-- TOC entry 400 (class 1247 OID 29494)
-- Dependencies: 5
-- Name: dm_exercicio; Type: DOMAIN; Schema: public; Owner: postgres
--

CREATE DOMAIN dm_exercicio AS smallint;


ALTER DOMAIN public.dm_exercicio OWNER TO postgres;

--
-- TOC entry 401 (class 1247 OID 29495)
-- Dependencies: 402 5
-- Name: dm_logico; Type: DOMAIN; Schema: public; Owner: postgres
--

CREATE DOMAIN dm_logico AS character varying(1)
	CONSTRAINT dm_logico_check CHECK ((((VALUE)::text = ANY (ARRAY[('*'::character varying)::text, (''::character varying)::text])) OR (VALUE IS NULL)));


ALTER DOMAIN public.dm_logico OWNER TO postgres;

--
-- TOC entry 403 (class 1247 OID 29497)
-- Dependencies: 5
-- Name: dm_numero; Type: DOMAIN; Schema: public; Owner: postgres
--

CREATE DOMAIN dm_numero AS integer DEFAULT 0;


ALTER DOMAIN public.dm_numero OWNER TO postgres;

--
-- TOC entry 404 (class 1247 OID 29498)
-- Dependencies: 5
-- Name: dm_orgao; Type: DOMAIN; Schema: public; Owner: postgres
--

CREATE DOMAIN dm_orgao AS character varying(2);


ALTER DOMAIN public.dm_orgao OWNER TO postgres;

--
-- TOC entry 405 (class 1247 OID 29499)
-- Dependencies: 5
-- Name: dm_str10; Type: DOMAIN; Schema: public; Owner: postgres
--

CREATE DOMAIN dm_str10 AS character varying(10);


ALTER DOMAIN public.dm_str10 OWNER TO postgres;

--
-- TOC entry 406 (class 1247 OID 29500)
-- Dependencies: 5
-- Name: dm_textocurto; Type: DOMAIN; Schema: public; Owner: postgres
--

CREATE DOMAIN dm_textocurto AS character varying(255);


ALTER DOMAIN public.dm_textocurto OWNER TO postgres;

--
-- TOC entry 407 (class 1247 OID 29501)
-- Dependencies: 5
-- Name: dm_textomedio; Type: DOMAIN; Schema: public; Owner: postgres
--

CREATE DOMAIN dm_textomedio AS character varying(8000);


ALTER DOMAIN public.dm_textomedio OWNER TO postgres;

--
-- TOC entry 408 (class 1247 OID 29502)
-- Dependencies: 409 5
-- Name: dm_tipodiretiva; Type: DOMAIN; Schema: public; Owner: postgres
--

CREATE DOMAIN dm_tipodiretiva AS character varying(10) NOT NULL DEFAULT 'MENU'::character varying
	CONSTRAINT dm_tipodiretiva_check CHECK (((VALUE)::text = ANY (ARRAY[('MENU'::character varying)::text, ('CONTROLE'::character varying)::text, ('OPERACAO'::character varying)::text, ('FILTRO'::character varying)::text, ('RESTRICAO'::character varying)::text])));


ALTER DOMAIN public.dm_tipodiretiva OWNER TO postgres;

--
-- TOC entry 410 (class 1247 OID 29504)
-- Dependencies: 411 5
-- Name: dm_tipousuario; Type: DOMAIN; Schema: public; Owner: postgres
--

CREATE DOMAIN dm_tipousuario AS character varying(10) NOT NULL DEFAULT 'USUARIO'::character varying
	CONSTRAINT dm_tipousuario_check CHECK (((VALUE)::text = ANY (ARRAY[('GRUPO'::character varying)::text, ('USUARIO'::character varying)::text])));


ALTER DOMAIN public.dm_tipousuario OWNER TO postgres;

SET search_path = teste, pg_catalog;

--
-- TOC entry 434 (class 1247 OID 36891)
-- Dependencies: 132
-- Name: tp_teste; Type: TYPE; Schema: teste; Owner: postgres
--

CREATE TYPE tp_teste AS (
	cod integer,
	nome character varying(200)
);


ALTER TYPE teste.tp_teste OWNER TO postgres;

SET search_path = public, pg_catalog;

--
-- TOC entry 148 (class 1255 OID 29506)
-- Dependencies: 438 5
-- Name: sp_listar_fks(character varying); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION sp_listar_fks(dependencia_orgao_exercicio character varying, OUT seq integer, OUT tabelamestre character varying, OUT campomestre character varying, OUT tabeladetalhe character varying, OUT campodetalhe character varying) RETURNS SETOF record
    LANGUAGE plpgsql
    AS $$
DECLARE
  V_CONSULTA RECORD;
BEGIN
  SEQ := 0;
  FOR V_CONSULTA IN
    SELECT
      MESTRE.OID AS OID_MESTRE,
      MESTRE.RELNAME AS TABELA_MESTRE,
      DETALHE.OID AS OID_DETALHE,
      DETALHE.RELNAME AS TABELA_DETALHE,
      RELACAO.CONFKEY AS CAMPOS_MESTRE,
      RELACAO.CONKEY AS CAMPOS_DETALHE
    FROM
      PG_CONSTRAINT RELACAO
      INNER JOIN PG_CLASS MESTRE ON MESTRE.OID = RELACAO.CONFRELID
      INNER JOIN PG_CLASS DETALHE ON DETALHE.OID = RELACAO.CONRELID
    WHERE
      UPPER(RELACAO.CONTYPE) = UPPER('F')
    ORDER BY
      MESTRE.RELNAME,
      DETALHE.RELNAME
    LOOP
    BEGIN
      SEQ := SEQ + 1;
      TABELAMESTRE := V_CONSULTA.TABELA_MESTRE;
      TABELADETALHE := V_CONSULTA.TABELA_DETALHE;

      FOR I IN 1..ARRAY_UPPER(V_CONSULTA.CAMPOS_MESTRE, 1) LOOP
        SELECT
          CAMPO.ATTNAME
        FROM
          PG_ATTRIBUTE CAMPO
        WHERE
          CAMPO.ATTNUM = V_CONSULTA.CAMPOS_MESTRE[I] AND
          CAMPO.ATTRELID = V_CONSULTA.OID_MESTRE
        INTO
          CAMPOMESTRE;

        SELECT
          CAMPO.ATTNAME
        FROM
          PG_ATTRIBUTE CAMPO
        WHERE
          CAMPO.ATTNUM = V_CONSULTA.CAMPOS_DETALHE[I] AND
          CAMPO.ATTRELID = V_CONSULTA.OID_DETALHE
        INTO
          CAMPODETALHE;

        IF ((UPPER(DEPENDENCIA_ORGAO_EXERCICIO) = UPPER('S')) OR (UPPER(CAMPODETALHE) <> UPPER('COD_ORGAOEXERCICIO'))) THEN
          RETURN NEXT;
        END IF;
      END LOOP;
    END;
  END LOOP;

  RETURN;
END;
$$;


ALTER FUNCTION public.sp_listar_fks(dependencia_orgao_exercicio character varying, OUT seq integer, OUT tabelamestre character varying, OUT campomestre character varying, OUT tabeladetalhe character varying, OUT campodetalhe character varying) OWNER TO postgres;

--
-- TOC entry 149 (class 1255 OID 29507)
-- Dependencies: 438 5
-- Name: sp_listar_tabelas(character varying); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION sp_listar_tabelas(listar_views character varying, OUT tabela character varying, OUT modulo character varying) RETURNS SETOF record
    LANGUAGE plpgsql
    AS $$
DECLARE
  V_CONSULTA RECORD;
  V_VIEW VARCHAR(1);
BEGIN
  V_VIEW := 'V';
  IF UPPER(LISTAR_VIEWS) = 'S' THEN
    V_VIEW := '';
  END IF;
  FOR V_CONSULTA IN
    SELECT
      UPPER(TRIM(PG_CLASS.RELNAME)) AS TAB
    FROM
      PG_CLASS
    WHERE
      UPPER(PG_CLASS.RELKIND) NOT IN ('I','S','T',V_VIEW) AND
      UPPER(PG_CLASS.RELNAME) NOT LIKE 'PG_%' AND
      UPPER(PG_CLASS.RELNAME) NOT LIKE 'SQL_%' AND
      UPPER(PG_CLASS.RELNAME) NOT LIKE 'SR_%'
    ORDER BY
      PG_CLASS.RELNAME
    LOOP
    BEGIN
      TABELA := V_CONSULTA.TAB;

      IF (SUBSTRING(TABELA FROM 1 FOR 3) = 'BUD') THEN
        MODULO := 'PLANEJAMENTO';
      ELSIF (SUBSTRING(TABELA FROM 1 FOR 3) = 'BUS') THEN
        MODULO := 'GESTÃO';
      ELSIF (SUBSTRING(TABELA FROM 1 FOR 3) = 'RES') THEN
        MODULO := 'RESULTADO';
      ELSIF (SUBSTRING(TABELA FROM 1 FOR 3) = 'VI_') THEN
        MODULO := 'VIEW';
      ELSE
        MODULO := 'SISTEMA';
      END IF;

      RETURN NEXT;
    END;
  END LOOP;

  RETURN;
END
$$;


ALTER FUNCTION public.sp_listar_tabelas(listar_views character varying, OUT tabela character varying, OUT modulo character varying) OWNER TO postgres;

SET search_path = teste, pg_catalog;

--
-- TOC entry 151 (class 1255 OID 36903)
-- Dependencies: 438 6
-- Name: fn_teste_in(integer); Type: FUNCTION; Schema: teste; Owner: postgres
--

CREATE FUNCTION fn_teste_in(iid integer, OUT oid integer, OUT oinfo character varying) RETURNS SETOF record
    LANGUAGE plpgsql
    AS $$declare
  r record;
begin
  for oid, oinfo in
    select id, info from teste.tb_teste where id <> iid
  loop
    return next;
  end loop;
  return;
end;$$;


ALTER FUNCTION teste.fn_teste_in(iid integer, OUT oid integer, OUT oinfo character varying) OWNER TO postgres;

--
-- TOC entry 152 (class 1255 OID 36904)
-- Dependencies: 434 438 6
-- Name: fn_teste_type(integer); Type: FUNCTION; Schema: teste; Owner: postgres
--

CREATE FUNCTION fn_teste_type(iid integer) RETURNS SETOF tp_teste
    LANGUAGE plpgsql
    AS $$
declare
  r record;
  t teste.tp_teste%ROWTYPE;
begin
  for r in
    select id, info from teste.tb_teste where id <> iid
  loop
    t.cod = r.id;
    t.nome = r.info;
    return next t;
  end loop;
  return;
end;$$;


ALTER FUNCTION teste.fn_teste_type(iid integer) OWNER TO postgres;

SET search_path = public, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- TOC entry 119 (class 1259 OID 29508)
-- Dependencies: 1672 386 387 407 387 386 386 386 5 387 399 399
-- Name: infoorgaoexercicio; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE infoorgaoexercicio (
    cod_orgaoexercicio dm_codigo NOT NULL,
    sessao dm_descnormal DEFAULT 'GERAL'::character varying NOT NULL,
    chave dm_descnormal NOT NULL,
    valor dm_textomedio,
    datinc dm_data,
    datalt dm_data,
    datexc dm_data,
    usuinc dm_codigo,
    usualt dm_codigo,
    usuexc dm_codigo
);


ALTER TABLE public.infoorgaoexercicio OWNER TO postgres;

--
-- TOC entry 120 (class 1259 OID 29514)
-- Dependencies: 1673 399 399 407 5
-- Name: infosistema; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE infosistema (
    sessao dm_descnormal DEFAULT 'GERAL'::character varying NOT NULL,
    chave dm_descnormal NOT NULL,
    valor dm_textomedio
);


ALTER TABLE public.infosistema OWNER TO postgres;

--
-- TOC entry 121 (class 1259 OID 29520)
-- Dependencies: 1674 388 388 387 387 387 386 386 386 5 386 399 401 399 403 388
-- Name: netproxy; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE netproxy (
    cod_netproxy dm_codigo NOT NULL,
    descricao dm_descnormal NOT NULL,
    habilitado dm_logico,
    servidor dm_descnormal,
    porta dm_numero,
    usuario dm_desccurto,
    senha dm_desccurto,
    socket dm_desccurto NOT NULL,
    datinc dm_data,
    datalt dm_data,
    datexc dm_data,
    usuinc dm_codigo,
    usualt dm_codigo,
    usuexc dm_codigo,
    CONSTRAINT ck_netproxy_socket CHECK (((socket)::text = ANY (ARRAY[('-'::character varying)::text, ('v4'::character varying)::text, ('v4a'::character varying)::text, ('v5'::character varying)::text])))
);


ALTER TABLE public.netproxy OWNER TO postgres;

--
-- TOC entry 122 (class 1259 OID 29526)
-- Dependencies: 1675 1676 1677 388 388 386 401 399 403 388 388 399 399 388 388 387 387 387 386 386 386 5 386 399
-- Name: netsmtp; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE netsmtp (
    cod_netsmtp dm_codigo NOT NULL,
    descricao dm_descnormal NOT NULL,
    proposito dm_desccurto NOT NULL,
    permissao dm_desccurto NOT NULL,
    cod_netproxy dm_codigo,
    autenticar dm_logico,
    servidor dm_descnormal NOT NULL,
    porta dm_numero,
    usuario dm_desccurto,
    senha dm_desccurto,
    email dm_descnormal NOT NULL,
    emaildesc dm_descnormal,
    seguranca dm_desccurto NOT NULL,
    infoextra dm_desccurto,
    datinc dm_data,
    datalt dm_data,
    datexc dm_data,
    usuinc dm_codigo,
    usualt dm_codigo,
    usuexc dm_codigo,
    CONSTRAINT ck_netsmtp_permissao CHECK (((permissao)::text = ANY (ARRAY[('restrito'::character varying)::text, ('compartilhado'::character varying)::text]))),
    CONSTRAINT ck_netsmtp_proposito CHECK (((proposito)::text = ANY (ARRAY[('interno'::character varying)::text, ('parametro'::character varying)::text, ('usuario'::character varying)::text, ('geral'::character varying)::text]))),
    CONSTRAINT ck_netsmtp_seguranca CHECK (((seguranca)::text = ANY (ARRAY[('-'::character varying)::text, ('tls1'::character varying)::text, ('ssl2'::character varying)::text, ('ssl23'::character varying)::text, ('ssl3'::character varying)::text])))
);


ALTER TABLE public.netsmtp OWNER TO postgres;

--
-- TOC entry 123 (class 1259 OID 29534)
-- Dependencies: 387 405 386 5 404 387 386 387 389 386
-- Name: orgao; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE orgao (
    orgao dm_orgao NOT NULL,
    descricao dm_desclongo NOT NULL,
    status dm_str10,
    datinc dm_data,
    datalt dm_data,
    datexc dm_data,
    usuinc dm_codigo,
    usualt dm_codigo,
    usuexc dm_codigo
);


ALTER TABLE public.orgao OWNER TO postgres;

--
-- TOC entry 124 (class 1259 OID 29539)
-- Dependencies: 386 404 400 389 405 401 387 386 386 5 387 387 386
-- Name: orgaoexercicio; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE orgaoexercicio (
    cod_orgaoexercicio dm_codigo NOT NULL,
    orgao dm_orgao NOT NULL,
    exercicio dm_exercicio NOT NULL,
    descricao dm_desclongo NOT NULL,
    status dm_str10,
    sistema_terceiro dm_logico,
    datinc dm_data,
    datalt dm_data,
    datexc dm_data,
    usuinc dm_codigo,
    usualt dm_codigo,
    usuexc dm_codigo
);


ALTER TABLE public.orgaoexercicio OWNER TO postgres;

--
-- TOC entry 125 (class 1259 OID 29544)
-- Dependencies: 5 401 386 387 387 401 387 386 386 401 406 401 403 389 399 388 386 401 410 386 388 401 387 401 387 407
-- Name: usuario; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE usuario (
    tipo dm_tipousuario,
    somleitura dm_logico,
    cod_usuario dm_codigo NOT NULL,
    usuario dm_desccurto NOT NULL,
    nome dm_descnormal,
    descricao dm_desclongo,
    prioridade dm_numero,
    gerenciador dm_logico,
    senha dm_textocurto,
    senhafixa dm_logico,
    senhaativa dm_logico,
    senhaprefixo dm_desccurto,
    senhaulttroca dm_data,
    senhahistorico dm_textomedio,
    acessoativo dm_logico,
    acessobloqueado dm_logico,
    acessoremovido dm_logico,
    expiracao dm_data,
    cod_netsmtp dm_codigo,
    datinc dm_data,
    datalt dm_data,
    datexc dm_data,
    usuinc dm_codigo,
    usualt dm_codigo,
    usuexc dm_codigo
);


ALTER TABLE public.usuario OWNER TO postgres;

--
-- TOC entry 126 (class 1259 OID 29549)
-- Dependencies: 387 5 386 386 386 387 387 407 406 386 386 401 408
-- Name: usuariodiretiva; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE usuariodiretiva (
    tipo dm_tipodiretiva,
    somleitura dm_logico,
    cod_usuariodiretiva dm_codigo NOT NULL,
    cod_usuario dm_codigo NOT NULL,
    diretiva dm_textocurto NOT NULL,
    valor dm_textomedio,
    datinc dm_data,
    datalt dm_data,
    datexc dm_data,
    usuinc dm_codigo,
    usualt dm_codigo,
    usuexc dm_codigo
);


ALTER TABLE public.usuariodiretiva OWNER TO postgres;

--
-- TOC entry 127 (class 1259 OID 29554)
-- Dependencies: 386 386 401 5 386 386 386 386 387 387 387
-- Name: usuariogrupo; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE usuariogrupo (
    somleitura dm_logico,
    cod_usuariogrupo dm_codigo NOT NULL,
    cod_usuario dm_codigo NOT NULL,
    cod_grupo dm_codigo NOT NULL,
    datinc dm_data,
    datalt dm_data,
    datexc dm_data,
    usuinc dm_codigo,
    usualt dm_codigo,
    usuexc dm_codigo
);


ALTER TABLE public.usuariogrupo OWNER TO postgres;

--
-- TOC entry 128 (class 1259 OID 29559)
-- Dependencies: 387 386 386 386 387 387 5 386 386 386
-- Name: usuarioorgao; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE usuarioorgao (
    cod_usuarioorgao dm_codigo NOT NULL,
    cod_usuario dm_codigo NOT NULL,
    cod_orgaoexercicio dm_codigo NOT NULL,
    datinc dm_data,
    datalt dm_data,
    datexc dm_data,
    usuinc dm_codigo,
    usualt dm_codigo,
    usuexc dm_codigo
);


ALTER TABLE public.usuarioorgao OWNER TO postgres;

--
-- TOC entry 129 (class 1259 OID 29561)
-- Dependencies: 1405 399 5 407 399
-- Name: vi_infosistema; Type: VIEW; Schema: public; Owner: postgres
--

CREATE VIEW vi_infosistema AS
    SELECT infosistema.sessao, infosistema.chave, infosistema.valor FROM infosistema;


ALTER TABLE public.vi_infosistema OWNER TO postgres;

--
-- TOC entry 130 (class 1259 OID 29564)
-- Dependencies: 1406 387 386 410 401 386 388 399 389 403 401 406 401 401 5 388 387 407 386 401 401 401 387 386 387 386 387
-- Name: vi_usuario; Type: VIEW; Schema: public; Owner: postgres
--

CREATE VIEW vi_usuario AS
    SELECT usuario.tipo, usuario.somleitura, usuario.cod_usuario, usuario.usuario, usuario.nome, usuario.descricao, usuario.prioridade, usuario.gerenciador, usuario.senha, usuario.senhafixa, usuario.senhaativa, usuario.senhaprefixo, usuario.senhaulttroca, usuario.senhahistorico, usuario.acessoativo, usuario.acessobloqueado, usuario.acessoremovido, usuario.expiracao, usuario.cod_netsmtp, usuario.datinc, usuario.datalt, usuario.datexc, usuario.usuinc, usuario.usualt, usuario.usuexc FROM usuario;


ALTER TABLE public.vi_usuario OWNER TO postgres;

--
-- TOC entry 131 (class 1259 OID 29567)
-- Dependencies: 1407 386 406 386 407 387 387 387 386 401 408 5 386 386
-- Name: vi_usuariodiretiva; Type: VIEW; Schema: public; Owner: postgres
--

CREATE VIEW vi_usuariodiretiva AS
    SELECT usuariodiretiva.tipo, usuariodiretiva.somleitura, usuariodiretiva.cod_usuariodiretiva, usuariodiretiva.cod_usuario, usuariodiretiva.diretiva, usuariodiretiva.valor, usuariodiretiva.datinc, usuariodiretiva.datalt, usuariodiretiva.datexc, usuariodiretiva.usuinc, usuariodiretiva.usualt, usuariodiretiva.usuexc FROM usuariodiretiva;


ALTER TABLE public.vi_usuariodiretiva OWNER TO postgres;

SET search_path = teste, pg_catalog;

--
-- TOC entry 133 (class 1259 OID 36892)
-- Dependencies: 6
-- Name: sq_teste; Type: SEQUENCE; Schema: teste; Owner: postgres
--

CREATE SEQUENCE sq_teste
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER TABLE teste.sq_teste OWNER TO postgres;

--
-- TOC entry 134 (class 1259 OID 36894)
-- Dependencies: 6
-- Name: tb_teste; Type: TABLE; Schema: teste; Owner: postgres; Tablespace: 
--

CREATE TABLE tb_teste (
    id integer NOT NULL,
    info character varying(200)
);


ALTER TABLE teste.tb_teste OWNER TO postgres;

--
-- TOC entry 135 (class 1259 OID 36898)
-- Dependencies: 1414 6
-- Name: vi_teste; Type: VIEW; Schema: teste; Owner: postgres
--

CREATE VIEW vi_teste AS
    SELECT tb_teste.id, tb_teste.info FROM tb_teste;


ALTER TABLE teste.vi_teste OWNER TO postgres;

SET search_path = public, pg_catalog;

--
-- TOC entry 1679 (class 2606 OID 29571)
-- Dependencies: 119 119 119 119
-- Name: pk_infoorgaoexercicio; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY infoorgaoexercicio
    ADD CONSTRAINT pk_infoorgaoexercicio PRIMARY KEY (cod_orgaoexercicio, sessao, chave);


--
-- TOC entry 1682 (class 2606 OID 29573)
-- Dependencies: 120 120 120
-- Name: pk_infosistema; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY infosistema
    ADD CONSTRAINT pk_infosistema PRIMARY KEY (sessao, chave);


--
-- TOC entry 1684 (class 2606 OID 29575)
-- Dependencies: 121 121
-- Name: pk_netroxy; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY netproxy
    ADD CONSTRAINT pk_netroxy PRIMARY KEY (cod_netproxy);


--
-- TOC entry 1686 (class 2606 OID 29577)
-- Dependencies: 122 122
-- Name: pk_netsmtp; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY netsmtp
    ADD CONSTRAINT pk_netsmtp PRIMARY KEY (cod_netsmtp);


--
-- TOC entry 1688 (class 2606 OID 29579)
-- Dependencies: 123 123
-- Name: pk_orgao; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY orgao
    ADD CONSTRAINT pk_orgao PRIMARY KEY (orgao);


--
-- TOC entry 1690 (class 2606 OID 29581)
-- Dependencies: 124 124
-- Name: pk_orgaoexercicio; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY orgaoexercicio
    ADD CONSTRAINT pk_orgaoexercicio PRIMARY KEY (cod_orgaoexercicio);


--
-- TOC entry 1694 (class 2606 OID 29583)
-- Dependencies: 125 125
-- Name: pk_usuario; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY usuario
    ADD CONSTRAINT pk_usuario PRIMARY KEY (cod_usuario);


--
-- TOC entry 1699 (class 2606 OID 29585)
-- Dependencies: 126 126
-- Name: pk_usuariodiretiva; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY usuariodiretiva
    ADD CONSTRAINT pk_usuariodiretiva PRIMARY KEY (cod_usuariodiretiva);


--
-- TOC entry 1701 (class 2606 OID 29587)
-- Dependencies: 127 127
-- Name: pk_usuariogrupo; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY usuariogrupo
    ADD CONSTRAINT pk_usuariogrupo PRIMARY KEY (cod_usuariogrupo);


--
-- TOC entry 1703 (class 2606 OID 29589)
-- Dependencies: 128 128
-- Name: pk_usuarioorgao; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY usuarioorgao
    ADD CONSTRAINT pk_usuarioorgao PRIMARY KEY (cod_usuarioorgao);


--
-- TOC entry 1692 (class 2606 OID 29591)
-- Dependencies: 124 124 124
-- Name: un_orgaoexercicio01; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY orgaoexercicio
    ADD CONSTRAINT un_orgaoexercicio01 UNIQUE (orgao, exercicio);


--
-- TOC entry 1696 (class 2606 OID 29593)
-- Dependencies: 125 125
-- Name: un_usuario; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY usuario
    ADD CONSTRAINT un_usuario UNIQUE (usuario);


SET search_path = teste, pg_catalog;

--
-- TOC entry 1705 (class 2606 OID 36897)
-- Dependencies: 134 134
-- Name: pk_teste; Type: CONSTRAINT; Schema: teste; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY tb_teste
    ADD CONSTRAINT pk_teste PRIMARY KEY (id);


SET search_path = public, pg_catalog;

--
-- TOC entry 1680 (class 1259 OID 29594)
-- Dependencies: 120 120
-- Name: id_infosistema; Type: INDEX; Schema: public; Owner: postgres; Tablespace: 
--

CREATE INDEX id_infosistema ON infosistema USING btree (sessao, chave);


--
-- TOC entry 1697 (class 1259 OID 29595)
-- Dependencies: 126
-- Name: id_usuariodiretiva; Type: INDEX; Schema: public; Owner: postgres; Tablespace: 
--

CREATE INDEX id_usuariodiretiva ON usuariodiretiva USING btree (diretiva);


--
-- TOC entry 1408 (class 2618 OID 29596)
-- Dependencies: 130 130 130 125
-- Name: rl_usuario_delete; Type: RULE; Schema: public; Owner: postgres
--

CREATE RULE rl_usuario_delete AS ON DELETE TO vi_usuario DO INSTEAD DELETE FROM usuario WHERE ((usuario.cod_usuario)::double precision = (old.cod_usuario)::double precision);


--
-- TOC entry 1409 (class 2618 OID 29597)
-- Dependencies: 130 130 130 130 130 130 130 130 130 130 130 130 130 130 130 130 130 130 125 130 130 130
-- Name: rl_usuario_insert; Type: RULE; Schema: public; Owner: postgres
--

CREATE RULE rl_usuario_insert AS ON INSERT TO vi_usuario DO INSTEAD INSERT INTO usuario (tipo, somleitura, cod_usuario, usuario, nome, descricao, prioridade, gerenciador, senha, senhafixa, senhaativa, senhaprefixo, senhaulttroca, senhahistorico, acessoativo, acessobloqueado, acessoremovido, expiracao, cod_netsmtp) VALUES (new.tipo, new.somleitura, new.cod_usuario, new.usuario, new.nome, new.descricao, new.prioridade, new.gerenciador, new.senha, new.senhafixa, new.senhaativa, new.senhaprefixo, new.senhaulttroca, new.senhahistorico, new.acessoativo, new.acessobloqueado, new.acessoremovido, new.expiracao, new.cod_netsmtp);


--
-- TOC entry 1410 (class 2618 OID 29598)
-- Dependencies: 130 130 130 125 130 130 130 130 130 130 130 130 130 130 130 130 130 130 130 130 130 130
-- Name: rl_usuario_update; Type: RULE; Schema: public; Owner: postgres
--

CREATE RULE rl_usuario_update AS ON UPDATE TO vi_usuario DO INSTEAD UPDATE usuario SET tipo = new.tipo, somleitura = new.somleitura, usuario = new.usuario, nome = new.nome, descricao = new.descricao, prioridade = new.prioridade, gerenciador = new.gerenciador, senha = new.senha, senhafixa = new.senhafixa, senhaativa = new.senhaativa, senhaprefixo = new.senhaprefixo, senhaulttroca = new.senhaulttroca, senhahistorico = new.senhahistorico, acessoativo = new.acessoativo, acessobloqueado = new.acessobloqueado, acessoremovido = new.acessoremovido, expiracao = new.expiracao, cod_netsmtp = new.cod_netsmtp WHERE ((usuario.cod_usuario)::double precision = (old.cod_usuario)::double precision);


--
-- TOC entry 1411 (class 2618 OID 29599)
-- Dependencies: 131 131 126 131
-- Name: rl_usuariodiretiva_delete; Type: RULE; Schema: public; Owner: postgres
--

CREATE RULE rl_usuariodiretiva_delete AS ON DELETE TO vi_usuariodiretiva DO INSTEAD DELETE FROM usuariodiretiva WHERE ((usuariodiretiva.cod_usuariodiretiva)::double precision = (old.cod_usuariodiretiva)::double precision);


--
-- TOC entry 1412 (class 2618 OID 29600)
-- Dependencies: 131 131 131 131 131 131 126 131 131
-- Name: rl_usuariodiretiva_insert; Type: RULE; Schema: public; Owner: postgres
--

CREATE RULE rl_usuariodiretiva_insert AS ON INSERT TO vi_usuariodiretiva DO INSTEAD INSERT INTO usuariodiretiva (tipo, somleitura, cod_usuariodiretiva, cod_usuario, diretiva, valor) VALUES (new.tipo, new.somleitura, new.cod_usuariodiretiva, new.cod_usuario, new.diretiva, new.valor);


--
-- TOC entry 1413 (class 2618 OID 29601)
-- Dependencies: 131 131 131 131 126 131 131 131 131
-- Name: rl_usuariodiretiva_update; Type: RULE; Schema: public; Owner: postgres
--

CREATE RULE rl_usuariodiretiva_update AS ON UPDATE TO vi_usuariodiretiva DO INSTEAD UPDATE usuariodiretiva SET tipo = new.tipo, somleitura = new.somleitura, cod_usuario = new.cod_usuario, diretiva = new.diretiva, valor = new.valor WHERE ((usuariodiretiva.cod_usuariodiretiva)::double precision = (old.cod_usuariodiretiva)::double precision);


SET search_path = teste, pg_catalog;

--
-- TOC entry 1415 (class 2618 OID 36901)
-- Dependencies: 135 135 135 135 134
-- Name: rl_teste_insert; Type: RULE; Schema: teste; Owner: postgres
--

CREATE RULE rl_teste_insert AS ON INSERT TO vi_teste DO INSTEAD INSERT INTO tb_teste (id, info) VALUES (new.id, new.info);


SET search_path = public, pg_catalog;

--
-- TOC entry 1706 (class 2606 OID 29602)
-- Dependencies: 124 1689 119
-- Name: fk_infoorgaoexercicio; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY infoorgaoexercicio
    ADD CONSTRAINT fk_infoorgaoexercicio FOREIGN KEY (cod_orgaoexercicio) REFERENCES orgaoexercicio(cod_orgaoexercicio);


--
-- TOC entry 1707 (class 2606 OID 29607)
-- Dependencies: 1693 125 121
-- Name: fk_netproxy_usualt; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY netproxy
    ADD CONSTRAINT fk_netproxy_usualt FOREIGN KEY (usualt) REFERENCES usuario(cod_usuario);


--
-- TOC entry 1708 (class 2606 OID 29612)
-- Dependencies: 121 125 1693
-- Name: fk_netproxy_usuexc; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY netproxy
    ADD CONSTRAINT fk_netproxy_usuexc FOREIGN KEY (usuexc) REFERENCES usuario(cod_usuario);


--
-- TOC entry 1709 (class 2606 OID 29617)
-- Dependencies: 125 121 1693
-- Name: fk_netproxy_usuinc; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY netproxy
    ADD CONSTRAINT fk_netproxy_usuinc FOREIGN KEY (usuinc) REFERENCES usuario(cod_usuario);


--
-- TOC entry 1710 (class 2606 OID 29622)
-- Dependencies: 122 1683 121
-- Name: fk_netsmtp_netproxy; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY netsmtp
    ADD CONSTRAINT fk_netsmtp_netproxy FOREIGN KEY (cod_netproxy) REFERENCES netproxy(cod_netproxy);


--
-- TOC entry 1711 (class 2606 OID 29627)
-- Dependencies: 125 1693 122
-- Name: fk_netsmtp_usualt; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY netsmtp
    ADD CONSTRAINT fk_netsmtp_usualt FOREIGN KEY (usualt) REFERENCES usuario(cod_usuario);


--
-- TOC entry 1712 (class 2606 OID 29632)
-- Dependencies: 122 125 1693
-- Name: fk_netsmtp_usuexc; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY netsmtp
    ADD CONSTRAINT fk_netsmtp_usuexc FOREIGN KEY (usuexc) REFERENCES usuario(cod_usuario);


--
-- TOC entry 1713 (class 2606 OID 29637)
-- Dependencies: 1693 122 125
-- Name: fk_netsmtp_usuinc; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY netsmtp
    ADD CONSTRAINT fk_netsmtp_usuinc FOREIGN KEY (usuinc) REFERENCES usuario(cod_usuario);


--
-- TOC entry 1714 (class 2606 OID 29642)
-- Dependencies: 124 123 1687
-- Name: fk_orgaoexercicio_orgao; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY orgaoexercicio
    ADD CONSTRAINT fk_orgaoexercicio_orgao FOREIGN KEY (orgao) REFERENCES orgao(orgao);


--
-- TOC entry 1715 (class 2606 OID 29647)
-- Dependencies: 125 1685 122
-- Name: fk_usuario_netsmtp; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY usuario
    ADD CONSTRAINT fk_usuario_netsmtp FOREIGN KEY (cod_netsmtp) REFERENCES netsmtp(cod_netsmtp);


--
-- TOC entry 1716 (class 2606 OID 29652)
-- Dependencies: 125 125 1693
-- Name: fk_usuario_usualt; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY usuario
    ADD CONSTRAINT fk_usuario_usualt FOREIGN KEY (usualt) REFERENCES usuario(cod_usuario);


--
-- TOC entry 1717 (class 2606 OID 29657)
-- Dependencies: 1693 125 125
-- Name: fk_usuario_usuexc; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY usuario
    ADD CONSTRAINT fk_usuario_usuexc FOREIGN KEY (usuexc) REFERENCES usuario(cod_usuario);


--
-- TOC entry 1718 (class 2606 OID 29662)
-- Dependencies: 125 1693 125
-- Name: fk_usuario_usuinc; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY usuario
    ADD CONSTRAINT fk_usuario_usuinc FOREIGN KEY (usuinc) REFERENCES usuario(cod_usuario);


--
-- TOC entry 1719 (class 2606 OID 29667)
-- Dependencies: 126 125 1693
-- Name: fk_usuariodiretiva_usualt; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY usuariodiretiva
    ADD CONSTRAINT fk_usuariodiretiva_usualt FOREIGN KEY (usualt) REFERENCES usuario(cod_usuario);


--
-- TOC entry 1720 (class 2606 OID 29672)
-- Dependencies: 126 125 1693
-- Name: fk_usuariodiretiva_usuario; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY usuariodiretiva
    ADD CONSTRAINT fk_usuariodiretiva_usuario FOREIGN KEY (cod_usuario) REFERENCES usuario(cod_usuario);


--
-- TOC entry 1721 (class 2606 OID 29677)
-- Dependencies: 1693 126 125
-- Name: fk_usuariodiretiva_usuexc; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY usuariodiretiva
    ADD CONSTRAINT fk_usuariodiretiva_usuexc FOREIGN KEY (usuexc) REFERENCES usuario(cod_usuario);


--
-- TOC entry 1722 (class 2606 OID 29682)
-- Dependencies: 126 1693 125
-- Name: fk_usuariodiretiva_usuinc; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY usuariodiretiva
    ADD CONSTRAINT fk_usuariodiretiva_usuinc FOREIGN KEY (usuinc) REFERENCES usuario(cod_usuario);


--
-- TOC entry 1723 (class 2606 OID 29687)
-- Dependencies: 125 1693 127
-- Name: fk_usuariogrupo_grupo; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY usuariogrupo
    ADD CONSTRAINT fk_usuariogrupo_grupo FOREIGN KEY (cod_grupo) REFERENCES usuario(cod_usuario);


--
-- TOC entry 1724 (class 2606 OID 29692)
-- Dependencies: 1693 127 125
-- Name: fk_usuariogrupo_usualt; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY usuariogrupo
    ADD CONSTRAINT fk_usuariogrupo_usualt FOREIGN KEY (usualt) REFERENCES usuario(cod_usuario);


--
-- TOC entry 1725 (class 2606 OID 29697)
-- Dependencies: 125 1693 127
-- Name: fk_usuariogrupo_usuario; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY usuariogrupo
    ADD CONSTRAINT fk_usuariogrupo_usuario FOREIGN KEY (cod_usuario) REFERENCES usuario(cod_usuario);


--
-- TOC entry 1726 (class 2606 OID 29702)
-- Dependencies: 127 1693 125
-- Name: fk_usuariogrupo_usuexc; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY usuariogrupo
    ADD CONSTRAINT fk_usuariogrupo_usuexc FOREIGN KEY (usuexc) REFERENCES usuario(cod_usuario);


--
-- TOC entry 1727 (class 2606 OID 29707)
-- Dependencies: 127 125 1693
-- Name: fk_usuariogrupo_usuinc; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY usuariogrupo
    ADD CONSTRAINT fk_usuariogrupo_usuinc FOREIGN KEY (usuinc) REFERENCES usuario(cod_usuario);


--
-- TOC entry 1728 (class 2606 OID 29712)
-- Dependencies: 128 1689 124
-- Name: fk_usuarioorgao_orgao; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY usuarioorgao
    ADD CONSTRAINT fk_usuarioorgao_orgao FOREIGN KEY (cod_orgaoexercicio) REFERENCES orgaoexercicio(cod_orgaoexercicio);


--
-- TOC entry 1729 (class 2606 OID 29717)
-- Dependencies: 1693 128 125
-- Name: fk_usuarioorgao_usualt; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY usuarioorgao
    ADD CONSTRAINT fk_usuarioorgao_usualt FOREIGN KEY (usualt) REFERENCES usuario(cod_usuario);


--
-- TOC entry 1730 (class 2606 OID 29722)
-- Dependencies: 1693 128 125
-- Name: fk_usuarioorgao_usuario; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY usuarioorgao
    ADD CONSTRAINT fk_usuarioorgao_usuario FOREIGN KEY (cod_usuario) REFERENCES usuario(cod_usuario);


--
-- TOC entry 1731 (class 2606 OID 29727)
-- Dependencies: 125 128 1693
-- Name: fk_usuarioorgao_usuexc; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY usuarioorgao
    ADD CONSTRAINT fk_usuarioorgao_usuexc FOREIGN KEY (usuexc) REFERENCES usuario(cod_usuario);


--
-- TOC entry 1732 (class 2606 OID 29732)
-- Dependencies: 128 1693 125
-- Name: fk_usuarioorgao_usuinc; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY usuarioorgao
    ADD CONSTRAINT fk_usuarioorgao_usuinc FOREIGN KEY (usuinc) REFERENCES usuario(cod_usuario);


--
-- TOC entry 1737 (class 0 OID 0)
-- Dependencies: 5
-- Name: public; Type: ACL; Schema: -; Owner: postgres
--

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


-- Completed on 2013-12-26 12:31:08

--
-- PostgreSQL database dump complete
--