4 ms·
An alternate way of doing this is to pass the entire current table row to a function which can be done easily: if you have a table "purchase" PG also creates a
by Erwin 7y ago
An alternate way of doing this is to pass the entire current table row to a function which can be done easily: if you have a table "purchase" PG also creates a "purchase" type, so if you have this function:
CREATE FUNCTION vat(a purchase) RETURNS numeric AS 'SELECT a.value * .25' LANGUAGE 'sql'
Then you can run:
SELECT value, vat(purchase.*) FROM purchase;
And so be able to use every purchase column within the SQL function to do your calculation.
There are two interesting shortcuts: You can call just vat(purchase) because the type is an alias for the current table row. That alias is very confusing and this is not recommended (try select table from table!)
There's also a method like shortcut which the documentation calls "functional notation":
SELECT value, purchase.vat FROM purchase;
This calls your VAT() function passing it the entire purchase row, see https://www.postgresql.org/docs/11/rowtypes.html#ROWTYPES-USAGE https://www.postgresql.org/docs/11/rowtypes.html#ROWTYPES-US...
I don't actually know if PG can correctly inline the necessary code in here -- it is better able to do it if you use "SQL" function certainly.
- philliphaydon 7y agoThis isn’t a good example because you wouldn’t pre calculate vat and store it for product listing, as vat doesn’t apply to all countries and it’s subject to change, and differ between countries. (Japan just changed gst yesterday) You also wouldn’t want to run a function on something you need to filter against. To give you an example we have the concept of a “deadline” date which is based on the time the record is stored + a period of time which it must be completed by. Calculating that column in sql before doing a where filter is crazy slow when you’re looking at millions and millions of records. But pre-calculating it and storing it, and then adding an index on top of it, is insanely fast. This is currently done in code. But if I moved this to a computed column then I can Ensure the result is always up to date if the period changes and avoid code being written to accidentally forget to update this value. There are use cases for computer columns. As there are for functions. And doing it in code. This feature in pg12 mainly gives us the ability to index the value which we couldn’t do before.
- taffer 7y agoI'm not sure if I understand you correctly, but logically the WHERE clause happens before the SELECT clause[1], so it only calculates this value for the rows you're interested in. It is also possible to index functions without generated columns. [1] https://blog.jooq.org/2016/12/09/a-beginners-guide-to-the-true-order-of-sql-operations/ https://blog.jooq.org/2016/12/09/a-beginners-guide-to-the-tr...
- philliphaydon 7y agoThe first part I’m saying is a bad example because you wouldn’t store the price + vat in a column let alone a calculated column. The second part I’m giving an example where doing: where created + period > now() - '3 days'::interval Having to calculate the value in a where clause is inefficient. Making a calculated column adding created and period then indexing it is more efficient.
- smilliken 7y agoThis has already been possible since PostgreSQL allows indexing over an expression: CREATE INDEX myindex ON mytable (myfunc(mytable));
- deleted 7y ago[deleted]
- cryptonector 7y agoThis is a workaround for not having computed columns, and you can even use a VIEW to present an interface that looks a lot like a table with computed columns, so it's pretty good, but you don't get persistence (the column will be computed every time it's required) and you don't get indexing. Another workaround is to create a proper column and then ON INSERT OR UPDATE triggers that force NEW.that_column to have a computed value. This approach gets you persistent computed columns that you can index on. What's surprising is that the new functionality in PG 12 doesn't support non-persistent computed columns, and that the other limitations (which are good) don't quite jive with the second workaround I mentioned.