Hello, I need your help because I have a materialized view not created during an import. This materialized view is using a function and this function depends on a table. When importing the database dump: - the function is created - the materialized view is not created because the table used by the function is imported yet - the table is created
Here is the code (in the correct order): CREATE TABLE user_corporation_rights ( id integer NOT NULL, -- ... ); CREATE OR REPLACE FUNCTION get_allowed_news_for_user_corporation(user_corporation_id_param integer) RETURNS character varying[] AS $BODY$ SELECT id FROM user_corporation_rights --... $BODY$ LANGUAGE sql VOLATILE; CREATE MATERIALIZED VIEW news_rights_by_user_corporation AS SELECT uc.id AS user_corporation_id, get_allowed_news_for_user_corporation( uc.id) AS news_list FROM user_corporation uc WITH DATA; The Postgresql version is 9.5. I think pg_dump doesn't know that the table is used in the function for the materialized view so it puts it at the end of the file because of its name. I tried to export/import the database in a .sql file and in a binary file but it's the same problem. For the moment, I think about 2 solutions: - export the materialized views separately of the tables/functions/data - maintain the materialized view definition in a .sql file to import after each pg_restore Do you have a better solution? Thank you, Aurélien Praga
