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.
surfaces
, where you have a set of indexed columns (state
,zipcode
&name
) to query by semantics.