--
--

SET escape_string_warning = off;

--
-- Name: miesquema; Type: SCHEMA; Schema: -; Owner: postgres
--

CREATE SCHEMA miesquema;


ALTER SCHEMA miesquema OWNER TO postgres;

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

CREATE PROCEDURAL LANGUAGE plpgsql;


ALTER PROCEDURAL LANGUAGE plpgsql OWNER TO postgres;




--
-- Name: foo; Type: TABLE; Schema: miesquema; Owner: postgres; Tablespace: 
--

CREATE TABLE foo (
    id integer
);


ALTER TABLE miesquema.foo OWNER TO postgres;


--
-- Name: empleados; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE empleados (
    id integer NOT NULL,
    nombre character varying(20),
    apellidos character varying(20)
);


ALTER TABLE public.empleados OWNER TO postgres;

--
-- Name: ficheros; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE ficheros (
    id integer NOT NULL,
    nombre character varying(20)
);


ALTER TABLE public.ficheros OWNER TO postgres;

--
-- Name: ficheros_empleados; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE ficheros_empleados (
    empleado integer,
    fichero integer
);


ALTER TABLE public.ficheros_empleados OWNER TO postgres;

--
-- Name: concat(text, text); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION concat(text, text) RETURNS text
    AS $_$
begin
  if $1 is null then
    return $2;
  end if;
  if $2 is null then
    return $1;
  end if;
  return $1 || ' ' || $2;
end $_$
    LANGUAGE plpgsql IMMUTABLE;


ALTER FUNCTION public.concat(text, text) OWNER TO postgres;

--
-- Name: text_concat(text); Type: AGGREGATE; Schema: public; Owner: postgres
--

CREATE AGGREGATE text_concat(text) (
    SFUNC = concat,
    STYPE = text
);


ALTER AGGREGATE public.text_concat(text) OWNER TO postgres;

--
-- Name: vista_empleados_con_archivos; Type: VIEW; Schema: public; Owner: postgres
--

CREATE VIEW vista_empleados_con_archivos AS
    SELECT (((e.nombre)::text || ' '::text) || (e.apellidos)::text) AS empleado, text_concat((f.nombre)::text) AS ficheros FROM empleados e, ficheros f, ficheros_empleados fe WHERE ((e.id = fe.empleado) AND (f.id = fe.fichero)) GROUP BY e.nombre, e.apellidos;


ALTER TABLE public.vista_empleados_con_archivos OWNER TO postgres;

--
-- Name: vista_empleados_con_y_sin_archivos; Type: VIEW; Schema: public; Owner: postgres
--

CREATE VIEW vista_empleados_con_y_sin_archivos AS
    SELECT a.empleado, text_concat((f.nombre)::text) AS ficheros FROM ((SELECT (((e.nombre)::text || ' '::text) || (e.apellidos)::text) AS empleado, fe.fichero AS id FROM (empleados e LEFT JOIN ficheros_empleados fe ON ((e.id = fe.empleado)))) a LEFT JOIN ficheros f ON ((f.id = a.id))) GROUP BY a.empleado;


ALTER TABLE public.vista_empleados_con_y_sin_archivos OWNER TO postgres;

--
-- Name: empleados_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE empleados_id_seq
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER TABLE public.empleados_id_seq OWNER TO postgres;

--
-- Name: empleados_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE empleados_id_seq OWNED BY empleados.id;


--
-- Name: empleados_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
--

SELECT pg_catalog.setval('empleados_id_seq', 5, true);


--
-- Name: ficheros_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE ficheros_id_seq
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER TABLE public.ficheros_id_seq OWNER TO postgres;

--
-- Name: ficheros_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE ficheros_id_seq OWNED BY ficheros.id;


--
-- Name: ficheros_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
--

SELECT pg_catalog.setval('ficheros_id_seq', 6, true);


--
-- Name: id; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE empleados ALTER COLUMN id SET DEFAULT nextval('empleados_id_seq'::regclass);


--
-- Name: id; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE ficheros ALTER COLUMN id SET DEFAULT nextval('ficheros_id_seq'::regclass);



--
-- Data for Name: foo; Type: TABLE DATA; Schema: miesquema; Owner: postgres
--

COPY foo (id) FROM stdin;
\.



--
-- Data for Name: empleados; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY empleados (id, nombre, apellidos) FROM stdin;
1	Elias	Figueroa
2	Marcelo	Salas
3	Pipo	Gorosito
4	Beto	Acosta
5	Candonga	Carreño
\.


--
-- Data for Name: ficheros; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY ficheros (id, nombre) FROM stdin;
4	Sophie_Moone.ogg
5	Puma_Sweet.ogg
6	Silvia_Saint.ogg
\.


--
-- Data for Name: ficheros_empleados; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY ficheros_empleados (empleado, fichero) FROM stdin;
1	4
1	5
1	6
2	5
2	6
3	6
4	4
4	5
4	6
\.


--
-- Name: empleados_nombre_key; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY empleados
    ADD CONSTRAINT empleados_nombre_key UNIQUE (nombre, apellidos);


--
-- Name: empleados_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY empleados
    ADD CONSTRAINT empleados_pkey PRIMARY KEY (id);


--
-- Name: ficheros_nombre_key; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY ficheros
    ADD CONSTRAINT ficheros_nombre_key UNIQUE (nombre);


--
-- Name: ficheros_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY ficheros
    ADD CONSTRAINT ficheros_pkey PRIMARY KEY (id);


--
-- Name: ficheros_empleados_empleado_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY ficheros_empleados
    ADD CONSTRAINT ficheros_empleados_empleado_fkey FOREIGN KEY (empleado) REFERENCES empleados(id) ON DELETE CASCADE;


--
-- Name: ficheros_empleados_fichero_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY ficheros_empleados
    ADD CONSTRAINT ficheros_empleados_fichero_fkey FOREIGN KEY (fichero) REFERENCES ficheros(id) ON DELETE CASCADE;


--
-- Name: miesquema; Type: ACL; Schema: -; Owner: postgres
--

REVOKE ALL ON SCHEMA miesquema FROM PUBLIC;
REVOKE ALL ON SCHEMA miesquema FROM postgres;
GRANT ALL ON SCHEMA miesquema TO postgres;
GRANT USAGE ON SCHEMA miesquema TO asterion;


--
-- 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;



--
-- Name: foo; Type: ACL; Schema: miesquema; Owner: postgres
--

REVOKE ALL ON TABLE foo FROM PUBLIC;
REVOKE ALL ON TABLE foo FROM postgres;
GRANT ALL ON TABLE foo TO postgres;
GRANT SELECT ON TABLE foo TO asterion;


--
--

