Here is what I am trying to do:
CREATE OR REPLACE FUNCTION getViewName ()
RETURNS text LANGUAGE plpgsql AS $name$
DECLARE
name text ;
BEGIN
SELECT 'test_schema.backup_' || to_char(CURRENT_DATE,'yyyymmdd') into name ;
RETURN name ;
END ; $name$;
which creates a function successfully.
=> select getViewName();
getviewname
-------------------------------
test_schema.backup_20190108
(1 row)
but when I try this:
=> create or replace view getViewName() AS select * from test_schema.backup ;
ERROR: syntax error at or near ")"
LINE 1: create or replace view getViewName() AS select * from ...
Can I not use the return of the function as my view name?
I don't want to use a solution like this, because in reality code will look like :
create or replace view getViewName() AS select
.....
where .... is over 1k lines of code and I don't want to put it inside a format.
Are there any alternative?
Edit:
I tried creating the temporary view with a fixed name.
Then do something like:
DO
$$
BEGIN
EXECUTE format( 'CREATE OR REPLACE VIEW %I '
' AS SELECT * FROM test_schema.mytemp_view', getViewName() ) ;
END ;
$$ LANGUAGE plpgsql ;
But then the problem is that I cannot drop mytemp_view since the new dynamic view is dependent on it.
