PostGIS view on multiple geometry columns

Question

I have two PostGIS tables, ‘filelinks’ and ‘maricopa_plss_qtrqtrs’. The filelinks table has a geometry type column named ‘geom_p’ and the maricopa_plss_qtrqtrs has a geometry type column named ‘geom’. The filelinks table can contain its own polygon geometry in geom_p, but if that geom_p value is null, then its ‘parent_ufid’ value will contain the ‘ufid’ value from maricopa_plss_qtrqtrs, which always has a populated geom field.

What I want to do is create a view (‘recursive_view’) that pulls the geometry from either the filelinks table geom_p (if it’s not null) or the maricopa_plss_qtrqtrs table’s geom value, related through the parent_ufid. Here’s what I have as the current view definition:

CREATE OR REPLACE VIEW gis.recursive_view AS
SELECT 
    f.ufid,
    qq.geom,
    f.geom_p,
    f.name,
    f.uri,
    f.type,
    f.date
FROM gis.filelinks f, gis.maricopa_plss_qtrqtrs qq
WHERE qq.ufid = f.parent_ufid or f.geom_p is not null;

The error I’m getting is:

ERROR: cannot change name of view column "geom_p" to "geom" ********** Error ********** ERROR: cannot change name of view column "geom_p" to "geom" SQL state: 42P16

I thought a PostGIS view can have multiple geometry columns, just like a table, no? What is the problem with my view definition then?

Answer 1

If you've already created the view, you cannot create/replace it and add a new column. You must either drop the view then re-create it or create a view with a different name.