I have a database containing the official cadastral surveying of hundreds of counties. For every county I have created a new schema named state_zipcode_countyname. As all my db entries have the same structure, I would like to create distinct views using the underlying relationships.

For example, the surfaces' type definition is stored in an external table "surftype". Thus, over the following code I can create a new view to use:

SELECT p.fid,
   FROM state1_12345_mycounty.surfaces p,
    state1_12345_mycounty.surftype a
  WHERE (a.fid = p.atype);

However, as I have hundreds of schemas and multiple views per schema, I would like to omit defining "state1_12345_mycounty" in every view. Instead, I'd like to only browse the schema in which the view is stored, so that I can copy-paste my views quicker and easier.

  • 1
    Without knowing the why's of your schema galore, to me this seems to be an XY-Problem; it is an anti-pattern in relational data analytics to introduce a folder-like hierarchy to split data semantically, and as you can see, it is rather tedious to work with. You'd be better off (in every way: handling, performance, maintenance) with having a single table of all your surfaces, where you have a set of indexed columns (state, zipcode & name) to query by semantics.
    – geozelot
    Feb 11 at 8:56
  • Write the sql view statement without the schema and set the search-path (i.e. default schema) with "SET search_path = MySchemaName;". Then execute your create view sql code and it will be created using the default schema. Feb 11 at 15:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.