Hello,
I have some question about pg_dump, pg_restore.
At the end of this text is full dump of database db_test.
This database has one table with one field named id_kotuc.
Default value for this field is function named fn_sq_id_kotuc().
Function and table is in same schema named moja_schema.
Before pg_dump, default value is: ... DEFAULT moja_schema.fn_sq_id_kotuc() ...
After pg_restore, default value is: ... DEFAULT fn_sq_id_kotuc() ...
The name of the scheme is missing, it is cut off.
I need first variant of default value (with name of the schema), because second
variant raise exception if I insert two or more records.
I don't know how can I use command pg_dump, if I want to dump it with the name
of the schema.
Can someone help me ?
THIS IS FULL DUMP OF DATABASE db_test (Win32, PostgreSQL v8.3.0)
------------------------------------------------------------------------------------------------------------------------------------------------------
--
-- PostgreSQL database dump
--
-- Started on 2008-02-12 12:20:56
SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;
--
-- TOC entry 1740 (class 1262 OID 36229)
-- Name: db_test; Type: DATABASE; Schema: -; Owner: postgres
-- Data Pos: 0
--
CREATE DATABASE db_test WITH TEMPLATE = template0 ENCODING = 'UTF8';
ALTER DATABASE db_test OWNER TO postgres;
\connect db_test
SET client_encoding = 'UTF8';
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 36230)
-- Name: moja_schema; Type: SCHEMA; Schema: -; Owner: postgres
-- Data Pos: 0
--
CREATE SCHEMA moja_schema;
ALTER SCHEMA moja_schema OWNER TO postgres;
--
-- TOC entry 1741 (class 0 OID 0)
-- Dependencies: 3
-- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: postgres
-- Data Pos: 0
--
COMMENT ON SCHEMA public IS 'standard public schema';
--
-- TOC entry 294 (class 2612 OID 16386)
-- Name: plpgsql; Type: PROCEDURAL LANGUAGE; Schema: -; Owner: postgres
-- Data Pos: 0
--
CREATE PROCEDURAL LANGUAGE plpgsql;
SET search_path = moja_schema, pg_catalog;
--
-- TOC entry 21 (class 1255 OID 36238)
-- Dependencies: 6 294
-- Name: fn_sq_id_kotuc(); Type: FUNCTION; Schema: moja_schema; Owner: postgres
-- Data Pos: 0
--
CREATE FUNCTION fn_sq_id_kotuc() RETURNS character varying
AS $$
DECLARE t_id VARCHAR;
BEGIN
t_id := 'KT' || LTrim( to_char( nextval( 'moja_schema.sq_id_kotuc' ),
'00000000' ) );
RETURN t_id;
END;
$$
LANGUAGE plpgsql;
ALTER FUNCTION moja_schema.fn_sq_id_kotuc() OWNER TO postgres;
SET default_tablespace = '';
SET default_with_oids = true;
--
-- TOC entry 1466 (class 1259 OID 36231)
-- Dependencies: 1734 6
-- Name: tb_tabulka; Type: TABLE; Schema: moja_schema; Owner: postgres;
Tablespace:
-- Data Pos: 0
--
------------------------------------------------------------------------------------------------
HERE IS PROBLEM.
I NEED RESTORE: '... DEFAULT moja_schema.fn_sq_id_kotuc() NOT NULL ....'
NOT: '... DEFAULT fn_sq_id_kotuc() NOT NULL ...'
I NEED RESTORE FUNCTION WITH SCHEMA NAME, NOT WITHOUT SCHEMA NAME.
------------------------------------------------------------------------------------------------
CREATE TABLE tb_tabulka (
id_kotuc character(10) DEFAULT fn_sq_id_kotuc() NOT NULL
);
ALTER TABLE moja_schema.tb_tabulka OWNER TO postgres;
--
-- TOC entry 1467 (class 1259 OID 36236)
-- Dependencies: 6
-- Name: sq_id_kotuc; Type: SEQUENCE; Schema: moja_schema; Owner: postgres
-- Data Pos: 0
--
CREATE SEQUENCE sq_id_kotuc
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;
ALTER TABLE moja_schema.sq_id_kotuc OWNER TO postgres;
--
-- TOC entry 1743 (class 0 OID 0)
-- Dependencies: 1467
-- Name: sq_id_kotuc; Type: SEQUENCE SET; Schema: moja_schema; Owner: postgres
-- Data Pos: 0
--
SELECT pg_catalog.setval('sq_id_kotuc', 2, true);
--
-- TOC entry 1737 (class 0 OID 36231)
-- Dependencies: 1466
-- Data for Name: tb_tabulka; Type: TABLE DATA; Schema: moja_schema; Owner:
postgres
-- Data Pos: 0
--
COPY tb_tabulka (id_kotuc) FROM stdin;
\.
--
-- TOC entry 1736 (class 2606 OID 36235)
-- Dependencies: 1466 1466
-- Name: tb_tabulka_pkey; Type: CONSTRAINT; Schema: moja_schema; Owner:
postgres; Tablespace:
-- Data Pos: 0
--
ALTER TABLE ONLY tb_tabulka
ADD CONSTRAINT tb_tabulka_pkey PRIMARY KEY (id_kotuc);
--
-- TOC entry 1742 (class 0 OID 0)
-- Dependencies: 3
-- Name: public; Type: ACL; Schema: -; Owner: postgres
-- Data Pos: 0
--
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 2008-02-13 00:08:39
--
-- PostgreSQL database dump complete
--
