global -> country -> account -> user

PostgreSQL doesn't support deep merging of json docs, so I just wrote an aggregate function using plv8 to merge them. (the result from the query always results in ~10 records which is then grouped and merged so perf is always fast)

https://gist.github.com/phillip-haydon/02e1cda346b4900a3e009...

This doesn't include the javascript used for merging cos I just wanted to show the stub.

This means we can just pass the args to postgresql and return the settings for any given user.

We used to attempt to run 6 queries and merge in C# code when we did this in SQL Server.

    SELECT COALESCE(companies.options, '{}'::jsonb) || COALESCE(groups.options, '{}'::jsonb) || COALESCE(users.options, '{}'::jsonb)
      FROM users
      JOIN companies ON users.company_id = companies.id
      LEFT JOIN groups ON users.group_id = groups.id
      WHERE users.id = ?

I sit right on the knife edge of tossing all the jsonb options and replacing with an old-school normalized EAV model.

It was a much more pleasant experience than having no stored procedures and a full blown ORM, which IMO inevitably leads to sillyness like doing inner joins at the UI level.

What ORM was this that made you do inner joins in the UI layer?

Deciding where business/application logic should reside is an important decision, and treating PostgreSQL as an application server in addition to a database does require a different way of approaching the tool. Of course, this is no different than any of the other application architecture choices that need to be made.

My initial comment was in response to these statements:

I have a friend who likes to do as much as possible with pgsql and I'm often surprised at how well it performs for the things he uses it for.

I understood you to read this as putting business logic in the database (indicated by the And yet) in your comment:

And yet i hear day after day, don't put business logic in the db

I read the initial comment by 'fapjacks as meaning using Postgres whenever appropriate, as opposed to doing everything one could with Postgres in Postgres.

Not a big deal at all. I saw a potential misunderstanding and attempted to clarify.

PS: Of course, business logic should go where I put it, right? ;)

As for the beginning of your comment, i am 100% with you. The problem is that the cases you describe (integrity,access,reports on data) are most of the time what the entire application does (we are all just doing CRUD and all that ...) and instead seeing that for what it is (let's call it DataLogic) people are all to often calling it Business logic, and the moment they put that label on it their brain automatically (after years of conditioning) does not even begin to consider the database as the appropriate place for that type of logic.

I know SQL has stored procedures and the like, but is it correct that SQL is limited in how it can express domain logic, compared to languages that backend application servers are generally written in?

Most of the time this is what people call domain logic (consistency/access/reports) although i would think a better name for it would be "data logic", heard that somewhere and in a lot of the cases this is almost everything the application does

Procedural extensions aren't, really. I mean you can literally just use Python (pg/python) or Javascript (plv8) to write postgres functions.