Re: Materialized view not created with import

On 07/05/2016 06:28 AM, Aurelien Praga wrote:
> 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 <http://uc.id> AS user_corporation_id,
> get_allowed_news_for_user_corporation(uc.id <http://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?

https://www.postgresql.org/docs/9.5/static/app-pgrestore.html

Using one of the custom dump formats:

"
-l
--list

     List the contents of the archive. The output of this operation can
be used as input to the -L option. Note that if filtering switches such
as -n or -t are used with -l, they will restrict the items listed.

-L list-file
--use-list=list-file

     Restore only those archive elements that are listed in list-file,
and restore them in the order they appear in the file. Note that if
filtering switches such as -n or -t are used with -L, they will further
restrict the items restored.

     list-file is normally created by editing the output of a previous
-l operation. Lines can be moved or removed, and can also be commented
out by placing a semicolon (;) at the start of the line. See below for
examples.

"
>
> Thank you,
>
> Aurélien Praga


--
Adrian Klaver
adrian.klaver@aklaver.com