On Sat, Jan 9, 2021 at 9:22 AM Markhof, Ingolf <ingolf.markhof@xxxxxxxxxxxxxx> wrote:
Tom, all,
when I change a tables column name in Oracle SQL , SQLdeveloper (a SQL client) marks views that refer to the table using the old column name as erroneous. So, I can easily identify these cases. And of course I, as a user, I am acting in my context, i.e. my schema. So it is perfectly clear what table I am referring to.
This highlights two major differences between Oracle and Postgres. Postgres will never allow you to make an invalid view. Also, the search_path in Postgres acts quite differently from the user context in Oracle. There is no guarantee that a user has a schema in postgres or that the schema would be in the search_path.
Please note: I am not developing any PL/SQL code. I don't have big development projects. I have more the role of an data analyst. I just create rather complex SQL queries which, from time to time, may need to be adopted to some new requirements. Or peers want to (re-) use (part of) my SQL queries. There is not really much versioning required.
What I understood so far is: I can use e.g. DBeaver to interact with PostgreSQL, to develop my SQL code. But I finally need to copy the SQL code into e.g. Github. Which is a manual process. I'd mark the SQL code in the DBeaver editor window and copy&paste it into some file in e.g. GitHub. Using Github, I'd get version control and other enhanced collaboration features which I don't really need. At the price that code transfer from SQL (DBeaver) to the code repository and vice versa is complete manually?! This doesn't really look like an enhancement.
Most likely, there are more professional ways to do that. I'd be glad to get advice.
What I would like to have is something that would automatically update the SQL code in the software repository when I run a CREATE OR REPLACE VIEW.
If you want to use source control (and I think it's a good idea), look into something like flywaydb or liquibase or any of the many other db schema control frameworks.
Ingolf
Verizon Deutschland GmbH - Sebrathweg 20, 44149 Dortmund, Germany - Amtsgericht Dortmund, HRB 14952 - Geschäftsführer: Detlef Eppig - Vorsitzender des Aufsichtsrats: Francesco de Maio
