Materialized view not created with import

[Aurelien Praga <aurelien.praga@xxxxxxxxxxxxxxx>]

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