Search code examples
sqlpostgresql

How to prevent PostgreSQL from altering my nicely formatted SQL definitions


I've noticed that after writing SQL for PostgreSQL, when I view the SQL definition later it seems the database changed the way I formatted the SQL. For example, my leading commas are moved to the back, my tabbing is altered, etc.

Is there a way to prevent this?


Solution

  • That's a misunderstanding. Postgres does not "alter the format". The original SQL string is just not stored at all.

    The query is parsed and rewritten and depending on the kind of query, action is taken. For instance, when you create view, the results are stored in system catalogs, mostly pg_class and pg_rewrite.

    You also must be aware that all identifiers are resolved at create time of a database object, taking the current search_path and visibility into account (early binding). The same query string can mean something different later, if the environment changes in any way.

    What you see later is a re-engineered version built from these entries. There are some built-in functions to help with that, but it's largely up to the client how to format reverse-engineered SQL code.

    Functions are an exception (partly). The function body is passed as string and saved as is, exactly as passed, with (currently) only superficial syntax testing and validation of objects. If you want a quick way to "save" a query including its format inside Postgres, you can use a PL/pgSQL function, for instance.

    The general approach is to have an independent code repository like @Mike already suggested - or not to care about the format too much.