PostgreSQL: Create View Name using parameter

Question

I want to parametrize the name of a view using postgresql. In particular, I want to create a view inside a function and use the parameter for the name of the view.

CREATE OR REPLACE FUNCTION function_to_be(certain_date  date)   RETURNS void as $$

BEGIN
CREATE OR REPLACE VIEW name_of_schema.test_view AS
SELECT * from test_table;   
END;

$$ LANGUAGE plpgsql;

So the desired name of the view should be name_of_schema.test_view_2019_07_29 after executing the function with the parameter .

However, the name should be schema-qualified - such that I could not directly pass a whole string for the name.

Many thanks in advance!

This sounds strange. What exactly are you trying to achieve with that? — user330315
– user330315, Commented Jul 29, 2019 at 12:53
I want to call the function and get a separate table each time for each certain_date. — Jogi
– Jogi, Commented Jul 29, 2019 at 12:58
Why create a view for each date then? At the end you wind up with thousands of views. You could create a function that returns the data you want by applying a restriction using the passed date. — user330315
– user330315, Commented Jul 29, 2019 at 13:00

sticky bit · Accepted Answer · 2019-07-29 17:01:08Z

2

It sounds a little stange to me having a lot of views in the end, that all are defined with the same query...

But you can try and use EXECUTE.

...
EXECUTE 'CREATE OR REPLACE VIEW name_of_schema.test_view_' || to_char(certain_date, 'yyyy_mm_dd') || ' AS SELECT * from test_table;';
...

edited Jul 29, 2019 at 17:01

answered Jul 29, 2019 at 12:51

sticky bit

37.7k12 gold badges34 silver badges46 bronze badges

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

Collectives™ on Stack Overflow

PostgreSQL: Create View Name using parameter

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related