2

This question is about finding approaches to solve a specific problem.

I have a table which has several metrics with values.

[metric1] [metric2] [metric3] [metric...N]

Those columns are fixed.

I need the user to be able to generate calculated metrics using the existing columns.

Let's say that a user made a custom metric which looks like:

[custom_metric] = ([metric1] + [metric3])*[metric2]

Therefore the user would be able to select a report using this custom metrics and other metrics as well.

I have two options. One is to manage this in the database which is a PostgreSQL. Other one is to split the math formula into pieces and store it which I think is the way to go. For PHP I'm using Laravel 5.1. The problem is how to store the formula in a way that could be easily applied when I'm generating the report.

1

This is probably one of the main reasons behind the invention of SQL stored procedures. A stored procedure is a function written by you, which is stored as an object in your database (if the software supports it). You can then call this as you would one of the built-in functions.

Postgres, which you mentioned, does have them. SQlite3, for example, doesn't.

The problem with stored procedures is that they are much less portable than your data definitions (i.e. tables, views, cursors, etc) - most often the used syntax is database-specific. I believe for Postgres most used is PL/pgSQL, which comes with your database installation, but has to be enabled in each new database instance that you create and need stored procedures with.

I think this site has good tutorials and explanations how to create stored procedures for Postgres, how to use in/out parameters and transform your logic into a result that your software can use: PostgresqlTutorial.com - Stored procedures section

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.