How can I dynamically create views in Postgres 10 using variables?

Question

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.

For example:

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 @[email protected] as
        select * from SHAREDSCHEMA.sometable where MYCOL = @mywhere@;
end $$;

Then each time I run that, I simply change @myschema and @mywhere.

Any help would be appreciated.

Thanks!

user1822 · Accepted Answer · 2021-05-03 14:41:38Z

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 $$;

The 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;

Worked a treat! Thanks!

cbmeeks
– cbmeeks

2021-05-03 15:05:58 +00:00
Commented May 3, 2021 at 15:05 — cbmeeks
– cbmeeks, Commented May 3, 2021 at 15:05

Stack Exchange Network

How can I dynamically create views in Postgres 10 using variables?

1 Answer 1

Your Answer

Hot Network Questions

How can I dynamically create views in Postgres 10 using variables?

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions