Pass cast type arguments to procedure with execute format

Question

I need to create multiple views on geospatial data each explicitly casting to the right geometry & I would like to create a helper procedure to do so like this:

CREATE PROCEDURE create_view_events(
  view_name TEXT, event_type TEXT, geo_type TEXT
) LANGUAGE plpgsql AS $$
BEGIN
  EXECUTE FORMAT('
     CREATE VIEW %I AS
     SELECT
       id,
       geom::%I AS geom   -- casting required, but how?
     FROM events
     WHERE type = %L
  ', view_name, geo_type, event_type);
END $$;
CALL create_view_events('events_viewX', 'X', GEOMETRY(POINT, 3347));

Manually running the CREATE VIEW statement with the casting replaced to geom::GEOMETRY(POINT, 3347) AS geom works & running the procedure without the casting also works. However calling through the procedure as-is yields this error I don't know what's going on.

ERROR: missing FROM-clause entry for table "GEOMETRY(POINT, 3347)"
  Where: PL/pgSQL function create_view_events(text,text,text) line 7 at EXECUTE

Answer 1

You're most likely missing the right flag for format(). Use %s instead of %I:

CREATE PROCEDURE create_view_events(
  view_name TEXT, event_type TEXT, geo_type TEXT) 
LANGUAGE plpgsql AS $$
BEGIN
  EXECUTE FORMAT('
     CREATE VIEW %I AS
     SELECT
       id,
       geom::%s AS geom   
     FROM events
     WHERE type = %L
  ', view_name, geo_type, event_type);
END $$;

CALL create_view_events('events_viewX', 'X', 'GEOMETRY(POINT, 3347)');

However, this query will most likely fail if the underlying SRS of geom is different than the one provided in the function parameter. If they do differ, consider using ST_Transform, otherwise just use ST_SetSRID

Demo: db<>fiddle

CREATE OR REPLACE PROCEDURE create_view_events(
  view_name TEXT, event_type TEXT, geo_type TEXT) 
LANGUAGE plpgsql AS $$
BEGIN
  EXECUTE FORMAT('
     CREATE OR REPLACE VIEW %I AS
     SELECT
       id,
       ST_SetSRID(geom,%s) AS geom  
     FROM events
     WHERE type = %L
  ', view_name, geo_type, event_type);
END $$;

CALL create_view_events('events_viewX', 'X', '3347');