Stored procedure in pgsql to create materialized views-CodePudding

I'm trying to create a stored procedure in pgsql that creates a materialized view with data from a specific year from a table. The parameters will be the table name, the column that contains the year, and the year.

I know that in SQL Server it would be something like this:

CREATE PROCEDURE createMaterializedView 
    @tablename varchar(100), 
    @column varchar(100), 
    @year integer
AS
BEGIN
    DECLARE @return varchar(1000) = 'CREATE MATERIALIZED VIEW view_'   @tablename   '_'   @year   'AS SELECT * from'   @tablename   'where'
      @column   ' = '   @year

    EXECUTE sp_executesql @return
END

In pgsql, what I got right now is this:

CREATE PROCEDURE createMaterializedView(tablename varchar(100), column varchar (100), year integer)
LANGUAGE 'plpgsql'
AS  $$

BEGIN
    CREATE MATERIALIZED VIEW "view_"   $tablename   "_"   $year 
    AS 
    SELECT * from tablename
    WHERE column = year
END;
$$;

Appreciate any help.

CodePudding user response：

Consider a dynamic query, formatted for your identifiers (view name, table, column) and literal value (year) to be run using EXECUTE.

CREATE OR REPLACE PROCEDURE create_materialized_view (
    _tablename varchar(100), 
    _column varchar(100), 
    _year integer
) 
   LANGUAGE 'plpgsql' AS 
$proc$ 
DECLARE 
    _vw text := 'view_' || _tablename || '_' || _year;
    _sql text := 
        'CREATE MATERIALIZED VIEW %I AS
         SELECT * FROM %I WHERE %I = %L';
BEGIN
    EXECUTE format(_sql, _vw, _tablename, _column, _year);
END
$proc$