2

Here is my setup. I have two schemas: my_app and static_data. The latter is imported from a static dump. For the needs of my application logic, I made views that use the tables of static_data, and I stored them in the my_app schema.

It all works great. But I need to update the static_data schema with a new dump, and have my views use the new data. The problem is, whatever I do, my views will always reference the old schema!

I tried importing the new dump in a new schema, static_data_new, then trying to delete static_data and rename static_data_new to static_data. It doesn't work because my views depend on tables in static_data, therefore PostgreSQL won't let me delete it.

Then I tried setting search_path to static_data_new. But when I do that, the views still reference the old tables!

Is it possible to have views that reference tables using the search_path? Thanks.

Artefact2
  • 7,516
  • 3
  • 30
  • 38

1 Answers1

3

Views are bound to the underlying objects. Renaming the object does not affect this link.
I see basically 3 different ways to deal with your problem:

  1. DELETE your views and re-CREATE them after you have your new table(s) in place. Simple and fast, as soon as you have your complete create script together. Don't forget to reset privileges, too. The recreate script may be tedious to compile, though.

  2. Use table-functions (functions RETURNING SETOF rows or RETURNING TABLE) instead of a views. Thereby you get "late binding": the object names will be looked up in the system catalogs at execution time, not at creation time. It will be your responsibility that those objects can, in fact, be found.

    The search_path can be pre-set per function or the search_path of the executing role will be effective for objects that are not explicitly schema-qualified. Detailed instructions and links in this related answer on SO.

    Functions are basically like prepared statements and behave subtly different from views. Details in this related answer on dba.SE.

  3. Take the TRUNCATE and INSERT route for the new data instead of DELETE and CREATE. Then all references stay intact. Find a more detailed answer about that here.

    If foreign keys reference your table you have to use DELETE FROM TABLE instead - or drop and recreate the foreign key constraints. it will be your responsibility that the referential integrity can be restored, or the recreation of the foreign key will fail.

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Thanks for your answer! I guess the simplest is to go with 1. and store the `my_app` tables in another schema. – Artefact2 May 12 '12 at 08:15
  • @Artefact2: I find [pgAdmin](http://www.pgadmin.org/) very useful for that kind of operation. It presents the complete drop and create scripts for a any selected object. – Erwin Brandstetter May 14 '12 at 09:41
  • Yes, I use it as well, though `pg_dump` and `pg_restore` are just more convenient for scripts & such. – Artefact2 May 14 '12 at 11:47