How force pg_dump to (not) include scheme name for each objects in DDL

Question

I need compare 2 DBs schemes (DDLs) - Postgress 9.5

Im executing below command on both servers:

pg_dump -U postgres --dbname=db--schema-only -f schema.sql

But I noticed one of output prefixes each objects by scheme name, eg

CREATE FUNCTION schemeName.function_name

while the other doesntm eg:

CREATE FUNCTION function_name

Is there any option in pg_dump where I can decide to inculde or not scheme names in output DDL? (Preference is at least remove those schema prefixes...)

Answer 1

In short:you can't. But, you can use sed to automate most of your editing.

---

#!/bin/sh

# dump only schema "tmp"
# force quoted identifiers
# use sed to strip them
# [youstillneedtoremove the "CReate SCHEMA $SCH_NAME-stuff

DB_NAME="postgres"

pg_dump -Upostgres -n tmp --schema-only --quote-all-identifiers $DB_NAME \
   | sed 's/"tmp"\.//g' > tmp_schema_stripped.sql

#EOF