--
-- PostgreSQL database dump
--

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

--
-- TOC entry 1490 (class 0 OID 0)
-- Name: DUMP TIMESTAMP; Type: DUMP TIMESTAMP; Schema: -; Owner: 
--

-- Started on 2004-10-29 15:02:15 中国标准时间


--
-- TOC entry 1493 (class 0 OID 0)
-- Dependencies: 5
-- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: jcl
--

COMMENT ON SCHEMA public IS 'Standard public schema';


SET search_path = public, pg_catalog;

--
-- TOC entry 17 (class 1255 OID 17347)
-- Dependencies: 5
-- Name: plpgsql_call_handler(); Type: FUNCTION; Schema: public; Owner: jcl
--

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


--
-- TOC entry 247 (class 16402 OID 17348)
-- Dependencies: 17
-- Name: plpgsql; Type: PROCEDURAL LANGUAGE; Schema: public; Owner: 
--

CREATE TRUSTED PROCEDURAL LANGUAGE plpgsql HANDLER plpgsql_call_handler;


--
-- TOC entry 18 (class 1255 OID 17349)
-- Dependencies: 5 247
-- Name: phone_01(); Type: FUNCTION; Schema: public; Owner: jcl
--

CREATE FUNCTION phone_01() RETURNS "trigger"
    AS $$
begin
if (select count(*) from phone_hot where s_id=NEW.s_id limit 1)<1
then
insert into phone_hot(s_id,id) values(NEW.s_id,NEW.id);
end if;
return NEW;
end;
$$
    LANGUAGE plpgsql;


--
-- TOC entry 19 (class 1255 OID 17350)
-- Dependencies: 5 247
-- Name: phone_10(); Type: FUNCTION; Schema: public; Owner: jcl
--

CREATE FUNCTION phone_10() RETURNS "trigger"
    AS $$
begin
if (select count(*) from phone where s_id=OLD.s_id limit 2)<=1
then
delete from phone_hot where s_id=OLD.s_id;
end if;
return OLD;
end;
$$
    LANGUAGE plpgsql;


SET default_with_oids = false;

--
-- TOC entry 1160 (class 1259 OID 17351)
-- Dependencies: 1476 5
-- Name: phone; Type: TABLE; Schema: public; Owner: jcl
--

CREATE TABLE phone (
    s_id integer NOT NULL,
    id character varying(11) NOT NULL,
    CONSTRAINT phone_check_id CHECK (((id)::text ~ '^[0-9]{7,}$'::text))
);


--
-- TOC entry 1161 (class 1259 OID 17354)
-- Dependencies: 5
-- Name: phone_hot; Type: TABLE; Schema: public; Owner: jcl
--

CREATE TABLE phone_hot (
    s_id integer NOT NULL,
    id character varying(11) NOT NULL
);


--
-- TOC entry 1162 (class 1259 OID 17356)
-- Dependencies: 5
-- Name: student; Type: TABLE; Schema: public; Owner: jcl
--

CREATE TABLE student (
    id integer NOT NULL,
    name character varying(8) NOT NULL
);


--
-- TOC entry 1482 (class 16386 OID 17359)
-- Dependencies: 1161 1161
-- Name: phone_hot_pkey; Type: CONSTRAINT; Schema: public; Owner: jcl
--

ALTER TABLE ONLY phone_hot
    ADD CONSTRAINT phone_hot_pkey PRIMARY KEY (s_id);


--
-- TOC entry 1479 (class 16386 OID 17361)
-- Dependencies: 1160 1160
-- Name: phone_pkey; Type: CONSTRAINT; Schema: public; Owner: jcl
--

ALTER TABLE ONLY phone
    ADD CONSTRAINT phone_pkey PRIMARY KEY (id);


--
-- TOC entry 1484 (class 16386 OID 17363)
-- Dependencies: 1162 1162
-- Name: student_name_key; Type: CONSTRAINT; Schema: public; Owner: jcl
--

ALTER TABLE ONLY student
    ADD CONSTRAINT student_name_key UNIQUE (name);


--
-- TOC entry 1486 (class 16386 OID 17365)
-- Dependencies: 1162 1162
-- Name: student_pkey; Type: CONSTRAINT; Schema: public; Owner: jcl
--

ALTER TABLE ONLY student
    ADD CONSTRAINT student_pkey PRIMARY KEY (id);


--
-- TOC entry 1480 (class 1259 OID 17366)
-- Dependencies: 1161
-- Name: phone_hot_id_key; Type: INDEX; Schema: public; Owner: jcl
--

CREATE UNIQUE INDEX phone_hot_id_key ON phone_hot USING btree (id);


--
-- TOC entry 1477 (class 1259 OID 17367)
-- Dependencies: 1160 1160
-- Name: phone_key; Type: INDEX; Schema: public; Owner: jcl
--

CREATE UNIQUE INDEX phone_key ON phone USING btree (s_id, id);


--
-- TOC entry 1488 (class 16386 OID 17370)
-- Dependencies: 1161 1162 1485
-- Name: phone_hot_s_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: jcl
--

ALTER TABLE ONLY phone_hot
    ADD CONSTRAINT phone_hot_s_id_fkey FOREIGN KEY (s_id) REFERENCES student(id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- TOC entry 1489 (class 16386 OID 17374)
-- Dependencies: 1161 1161 1160 1160 1477
-- Name: phone_hot_s_id_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: jcl
--

ALTER TABLE ONLY phone_hot
    ADD CONSTRAINT phone_hot_s_id_id_fkey FOREIGN KEY (s_id, id) REFERENCES phone(s_id, id) ON UPDATE RESTRICT ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED;


--
-- TOC entry 1487 (class 16386 OID 17378)
-- Dependencies: 1161 1481 1160
-- Name: phone_s_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: jcl
--

ALTER TABLE ONLY phone
    ADD CONSTRAINT phone_s_id_fkey FOREIGN KEY (s_id) REFERENCES phone_hot(s_id) ON UPDATE RESTRICT ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED;


--
-- TOC entry 1495 (class 0 OID 0)
-- Name: DUMP TIMESTAMP; Type: DUMP TIMESTAMP; Schema: -; Owner: 
--

-- Completed on 2004-10-29 15:02:15 中国标准时间


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

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