On Sat, Oct 19, 2013 at 11:37 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote: > For my work is very significant @a point - I wrote and I am writing usually > database centric stored procedures centric applications and @a works > perfect. For me a SQL code is code as any other - I use a my favourite > editor, I use a GIT for versioning, I can simple distributed application to > my customers.
What do you do to manage dependencies among the objects? How do you have your scripts set up to handle deploying, say, an extra column to an existing view?
I write a delta scripts - that ensure moving database structure to expected state. Now, you can use a infrastructure in PostgreSQL related to CREATE EXTENSION that is able to manage these delta scripts. But I wrote own (simple) three years ago. When you manage these scripts (together with regress tests) continuously, then it works - and delta scripts are a good self documentation again - there is usually different delta than GIT repository. I'll do more often commits in GIT than I delivery features to customer.
Our databases tended to be SP-heavy before I came along. I'm big on views because that allows my client code to do very specific queries without having to write new SPs all the time.
There is little bit different approach in PG than MSSQL. Some times is recommended to write procedures for all in MSSQL. PG is similar to Oracle. Procedures (functions) are written only for processes that changes data (or for triggers). For queries use a views mainly. Only when views cannot be used from some reasons (more complex security, some auditing) use a table functions.