I know how to do this in MySQL and MSSQL but I’m struggling to do the same in Postgres 10.12. I have about 200 views to create and the only difference in each one of them is the schema location and the where clause.
create or replace view SCHEMA1.myview as select * from SHAREDSCHEMA.sometable where MYCOL = 'ABC';
So what I need to do is create that view around 200 times with each iteration being:
SCHEMA1.myview SCHEMA2.myview SCHEMA3.myview etc... MYCOL = 'ABC' MYCOL = 'EFG' MYCOL = 'HIJ' etc...
Hope that makes sense. Again, the only difference between each iteration is the schema name changes and the where clause changes.
I could probably write some Python script or something to generate the code but I’d rather do it in Postgres if possible.
I know this isn’t valid code, but essentially this is what I am trying to do:
do $$ declare myschema varchar(10) := 'SCHEMA1'; mywhere varchar(3) := 'ABC'; begin create or replace view @myschema@.myview as select * from SHAREDSCHEMA.sometable where MYCOL = @mywhere@; end $$;
Then each time I run that, I simply change
Any help would be appreciated.
You need dynamic SQL for this:
do $$ declare myschema varchar(10) := 'SCHEMA1'; mywhere varchar(3) := 'ABC'; begin execute format(' create or replace view %I.myview as select * from SHAREDSCHEMA.sometable where MYCOL = %L', myschema, mywhere); end $$;
format() function is highly recommended when creating dynamic SQL to properly deal with identifiers and constant values.
You can also put this into a function (or procedure in newer versions):
create function create_view(p_schema text, p_value text) returns void as $$ begin execute format(' create or replace view %I.myview as select * from SHAREDSCHEMA.sometable where MYCOL = %L', p_schema, p_value); end $$ language plpgsql;
Then you can run:
select create_view('myschema', 'xyz');
If you want, you can use that to create all views in one go:
with data (schema_name, value) as values ('schema1', 'abc'), ('schema2', 'def'), ('schema3', 'ghi') ) select create_view(schema_name, value) from data;