5 ms·
One of the less-known behaviour (I'm reluctant to say "features") is that you can have some sort of virtual field in a table, that will execute a function when
by sntran 8y ago
One of the less-known behaviour (I'm reluctant to say "features") is that you can have some sort of virtual field in a table, that will execute a function when the field is accessed. This is due to the logic that PostgreSQL treats `row.property` the same as `property(row)`.
For example, we can have the function `full_name(person)` that returns the concatenation of `person.first_name` and `person.last_name`, and we can do `SELECT p.full_name FROM person p`. I think it's pretty neat.
- koolba 8y agoThat’s seriously cool. Does this simply look for the named function that takes the row type as a parameter and returns a scalar?
- Mister_Snuggles 8y agoThis is one of those things that seems like it would have limited value at first, but once you start to play with it you realize how powerful it actually is. The first database software that I was paid to work on was UniVerse. At the time it was owned by, I believe, VMark Software, but it changed hands a few times and is now part of the Rocket U2[0] family. It has an equivalent feature, which was very heavily used, called I-Descriptors. An I-Descriptor is an entry in a file's data dictionary that contained code to execute to calculate the value. You could generate a full name out of the firstname/lastname fields, perform conditional logic (e.g., return a specific address based on a preferred address field), etc. You could also call subroutines, where you had the full power of the BASIC language available. One of the interesting things I did was created a field which would perform geolocation based on the postal code portion of the address. It would split out the postal code, check a cache file for the data, call a web service to retrieve the data and cache it (if it wasn't in the cache already, otherwise it would just return the cached value), and return the results. Everything needed was built in to the database, it was just a matter of coding it. The best part was that it became just another thing you could do in the query language - answering the question "list all active clients in this geographic area" became just another query. [0] https://en.wikipedia.org/wiki/Rocket_U2 https://en.wikipedia.org/wiki/Rocket_U2
- deleted 8y ago[deleted]
- ris 8y agoI had no idea about this (and I've been using the `property(row)` pattern for years), but sure enough it is documented at the bottom of the section https://www.postgresql.org/docs/10/static/rowtypes.html#ROWTYPES-USAGE https://www.postgresql.org/docs/10/static/rowtypes.html#ROWT.... Given that it doesn't really let you do anything new, I've got to wonder if it's worth the potential confusion to people who haven't been able to find this obscure part of the documentation and will lose hours upon hours trying to find out where this table gets this "foobar" column defined...
- zaarn 8y agoWell, usually the first step in investigating the database is to look at the table definition (either in the migration or in the dump your database can produce), that usually clears up such confusions.
- steve-chavez 8y agoCool feature indeed, though keep in mind that for it to work you need to have the "virtual field" in the `search_path` of the session user. In PostgREST, we take advantage of this behavior to generate queries without the need for extra code to differentiate between a field or "virtual field", we call these "computed columns" though https://postgrest.org/en/v5.0/api.html#computed-columns https://postgrest.org/en/v5.0/api.html#computed-columns.