On Thu, Jul 8, 2021 at 1:29 PM Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
So the problem is not lack of a server feature, it's persuading pg_dump
to emit something other than what it does now.
So basically a different variation on the let someone else who feels hot enough about it and is able to code in C figure out a solution.
If schema qualification within the query is not possible (this seems to be the case given your previous statement that used the words messy and ugly) then the only other approach is to get a known good search_path in place before the CREATE VIEW command. Since that known good search path has a meaningful order, and the parsed view throws that knowledge away, it is not possible to use existing catalog data to solve the problem. We may not need a SET clause on CREATE VIEW but instead the session's order dependent search path can be stored alongside the view as critical metadata that tools can reference, including pg_dump. Assuming CREATE OR REPLACE VIEW would capture that metadata then an explicit SET option would not be necessary.
Though, part of the appeal of such an option is to localize the search_path change to just the view being created, leaving the session search_path alone. Its more of a usability thing, with potential metadata benefits, that probably doesn't offer enough improvement over just changing the session search_path.
David J.
