CODEWITHSUNDEEP
postgresqlpg-dump
Aaron
Aaron

Reputation: 1455

pg_dump excluded functions

I created a pg_dump with the following command - 

pg_dump -U postgres -d db -n public \
   --exclude-table-data 'exclude_table_*' \
   --exclude-table-data 'another_set_of_tables_to_exclude*' > dump.sql

This excluded the tables I needed it to exclude, but it didn't dump any functions that were in the public schema. Why did it not dump the functions and how do I get it to dump them?

UPDATE

This is the definition of a materialized view - 

CREATE MATERIALIZED VIEW public.attending AS
SELECT (split_part((ct.id)::text, '-'::text, 1))::bigint AS 
attending_physician,
split_part((ct.id)::text, '-'::text, 2) AS business,
(split_part((ct.id)::text, '-'::text, 3))::bigint AS organization,
split_part((ct.id)::text, '-'::text, 4) AS county,
ct.id,
ct."qtr-0",
ct."qtr-1",
ct."qtr-2",
ct."qtr-3",
ct."qtr-4",
ct."qtr-5",
ct."qtr-6",
ct."qtr-7",
ct."qtr-8" 
FROM crosstab('SELECT attending_practitioner || ''-'' || business || ''-'' || organization || ''-'' || county AS id, period, COALESCE(admits, 0) 
FROM   calc ORDER  BY 1, 2 DESC'::text, 'SELECT year || ''q'' || quarter FROM calc_trend ORDER BY 1 DESC limit 9'::text) ct(id character varying(32), "qtr-0" integer, "qtr-1" integer, "qtr-2" integer, "qtr-3" integer, "qtr-4" integer, "qtr-5" integer, "qtr-6" integer, "qtr-7" integer, "qtr-8" integer);

Upvotes: 4

Views: 2800

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247270

It should dump functions (and all other objects) in the public schema.

The functions that are not dumped are those that are part of an extension, like the crosstab in your case. Such objects are not dumped individually, they are included in the CREATE EXTENSION.

Unfortunately extensions are not dumped with a schema dump (they belong to the database).

You should create the extensions manually on the destination database before restoring the dump:

CREATE EXTENSION crosstab;

Upvotes: 3

Related Questions