SRID value=0 in geometry_columns for a view that explicitly uses ST_SetSRID

Question

PostgreSQL 15.2, PostGIS 3.3.2

I create a view which has geometry column composed of raw coordinates. I explicitly set the SRID. However, in geometry_columns view the SRID value is 0 for that view. How else can I force the SRID value?

CREATE VIEW a_view AS (
...
st_setsrid(st_makeline(ARRAY[st_makepoint(x1,y1), st_makepoint(x2,y2), st_makepoint(x3,y3)]), 3301) AS geometry
...

(Other views that reference geometries from tables get their SRID derived correctly.)

Answer 1

PostGIS requires a column registration via the PostgreSQL typemod system for objects not explicitly defined by DDL; registration can be inferred for objects known to the system via e.g. table definition DDL, for newly created types like in your View definition this is not possible.

The typemod registration can be invoked by an explicit CAST (::) to the fully qualified GEOMETRY|GEOGRAPHY(<GEOMETRY_TYPE>, <SRID>) data types (i.e. specifically including the <GEOMETRY_TYPE> & <SRID> typemods):

CREATE VIEW a_view AS (
  ...
  ST_SetSRID(
    ST_MakeLine(ARRAY[ST_MakePoint(x1,y1), ST_MakePoint(x2,y2), ST_MakePoint(x3,y3)]),
    3301
  )::GEOMETRY(LINESTRING, 3301) AS geometry
  ...