0

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,
    p.geometry,
    a.type
   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.

2
  • 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
    Commented Feb 11, 2021 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. Commented Feb 11, 2021 at 15:07

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.