How to create view with the name which can be pass through function as a parameter in postgresql?

Question

I am trying to create a view in postgreSQL using function. I want a view name in such a way that, which should be pass by the function as a parameter. For example..

--Table

create table test
( rollno int,
  name text);

--Function to create a view

create or replace function fun_view(roll int)
returns void as
$Body$
declare
       rec record;
begin
       for rec in select * from test where rollno=roll loop
           create or replace view "---Name of view should be roll---" as 
           select rollno from test;
       end loop;
$Body$
language plpgsql;

Craig Ringer · Accepted Answer · 2014-02-13 07:55:00Z

2

You need PL/PgSQL's dynamic SQL EXECUTE command:

EXECUTE format('create or replace view %I as ...', roll::text);

See: dynamic SQL in PL/PgSQL in the docs.

answered Feb 13, 2014 at 7:55

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sarfaraz Mak Over a year ago

Thank you so much Mr.Craig Ringer. It really works fine.

Collectives™ on Stack Overflow

How to create view with the name which can be pass through function as a parameter in postgresql?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related