pg_dump, pg_restore. - Mailing list pgsql-general

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