Problem Definition

I am trying to modified a shared development database, for a dev team to use for application development/testing.

Most of the collective work (table names, views, etc) are stored in the public schema, but I have set up schemas for each user to use as scratch space. However, the real goal would be for a user to use the objects in their schema if they exist and then fall back on the other objects, like how a search_path is used today.


This would probably be better described with an example. Let's say there's a database for an automotive repair application that a team is working on that has some tables and a view:

public.v_repairs  -- view that joins fields from all tables above

This is working great, but let's say a developer (e.g., Sally) wants to test a new feature out, with her own dataset to check for visual feedback, or threshold testing. She creates a table in her own schema, sally.schedule. Because the default search_path is something like "$user",public, any simple queries she creates, would first check her schema before public. For example:

SELECT * FROM schedule LEFT JOIN mechanics USING(mechanic_id);

This would use public.mechanics and sally.schedule, when sally is connected to the database. That's exactly the intended use, but when saving a view, it fully qualifies the table name by inserting the schema. So if the same query above was created as a view in the public schema it would look like:

SELECT * FROM public.schedule LEFT JOIN public.mechanics USING(mechanic_id);

The magic of search_path is negated. When Sally connects to the database to call the view (SELECT * FROM v_mechanics_schedule), it ignores the sally.schedule table she created, and only uses the public one.

Is there a way to not have Postgres store the schema name for table/view objects when saving a view?

Note: this is something new I was looking into, but something I've never really had the need for, since developers can typically clone applications, replicate databases, and work within their own sandbox environment. No need for some clever collaborative schema setup

  • I don't think this is possible. The only workaround I can think of is to put those select statements into (SQL) functions where the re-write wouldn't happen (and you can specify a search_path specific for the function)
    – user1822
    Dec 11, 2015 at 9:55

1 Answer 1


No. There is no way. I think there is a slight misconception of how things work.

Unlike functions, which store the function body as string (late binding!), a view parses the query and does not store the original text at all. Update: since Postgres 14 there are also SQL-standard functions (with BEGIN ATOMIC) which also parse the body at creation time! See:

All identifiers are resolved according to the current search_path and only their internal OIDs are saved (early binding!). That's also why SELECT * always resolves to the list of columns at the point in time when the VIEW was created.

What you see in pgAdmin or other clients when looking at the definition of a VIEW is a re-engineered SQL string. public.schedule will always be public.schedule and sally.schedule will always be sally.schedule (unless you rename table or schema). The (schema-qualified) names that are displayed are chosen such that the identifier is unambiguous with regard to the current search path.

This also shows when you change the names of columns, tables or schemas. The view keeps just working, because it does not depend on these attributions. The display of the definition is adapted dynamically.

Possible solution

If you want late binding you could use a function instead of a view and default to the current search path (one could SET the search path for the scope of a function to avoid that effect precisely.)

So instead of:

CREATE VIEW v_mechanics_schedule AS
FROM   schedule s  -- use aliases for short qualified column names in display
LEFT   JOIN mechanics m USING (mechanic_id);

Without schema-qualification, table names are resolved according to the current search_path at creation time.

You could:

CREATE FUNCTION f_mechanics_schedule()
  RETURNS TABLE (...)  -- spell out columns
SELECT * FROM schedule s  -- typically error prone
LEFT     JOIN mechanics m USING(mechanic_id);

Now, table names are resolved according to the search_path at execution time. Note that the same does not apply to the return type ( RETURNS TABLE (...)), which is determined at creation time. So this trick only works for compatible tables - the query has to return the same list of columns, only data types and the number of columns matter, column names are ignored. Only columns names defined in the RETURNS clause are visible outside the function.

That's also why SELECT * to return values in the function body is typically unreliable. If you change the definition of underlying tables, the function breaks: The defined return type remains the same, but SELECT * resolves to a different column list ...

OTOH, if you declare a function as RETURNS SETOF some_table (different syntax variant), a functional dependency is registered and the return type of the function is bound to the table definition, so SELECT * FROM some_table would make more sense.

This is also the reason why it is unsafe to have SECURITY DEFINER functions without fixing the search_path: Any user with the TEMP privilege can create a temp table that hides other tables because pg_temp comes first in the search path by default ...

You really need to understand the underlying mechanisms to play with this.

  • 1
    I would hesitate to use functions in this way. They are a maintenance nightmare. Although it may be a lot more overhead, since this is only for developmental purposes I would rather create middleware for the application to check existing tables/schemas and match those against the called view, dynamically recreating the SQL. What would be best is if Postgres had a "WITH LATE BINDING" clause to apply to CREATE VIEW to suit developmental purposes, or if there was a database setting that, when on, checks OIDs at runtime and, when off, uses the cache as it does now (per your explanation).
    – vol7ron
    Dec 11, 2015 at 17:57
  • @vol7ron, that's a very interesting and good idea. Have you proposed that on the PostgreSQL dev mailing list?
    – Wildcard
    Dec 23, 2017 at 0:49
  • I may have brought it up in IRC, but not on the mailing list — I stopped using that once it took too long to submit examples to the documentation
    – vol7ron
    Dec 23, 2017 at 0:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.