Using Postgres functions for reporting

Two weeks ago, when I was participating in the ORM panel I thought to myself that it has been a while since I’ve used Postgres functions to encapsulate some database functionality. And the reason for that was not that I am not really supporting the application at that time, but that I was trying to keep the structure of my data mart really clear and transparent.

There was one particular case where I knew for sure I should use functions instead of views, because I did have multiple views with identical logic which differ just by the state and the lender name. And I could not create  generic views and select from them, because in all those cases the condition won’t be pushed down.

But.. we were still validating results with our business stakeholders, and for some stupid reason I was thinking that I should wait till everything is final before converting. And then – I actually decided to convert to functions, because I could not stand a perspective of copying the same changes to four different places. And I did, and now I am thinking, why I was so stupid not to do it long time ago? I thought that it was not an immediate business need, but boy, how wrong I was!  It became so-so-so much easier to make the changes; also it turned out that Looker can work with the functions output just fine, no issues at all.

And as an extra bonus – since I always generate and execute dynamic SQL in the functions (for performance reasons), I do not need to recompile them when I need to change the underlying materialized views. No need to recompile 24 views! Isn’t it a relief?! Now I am thinking I should convert even more views to functions – as long as my team members are OK with that.

Leave a comment

Filed under Data management, SQL

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s