Comment by jerf
3 days ago
"Obviously querying over project_shorthand = '@unison/cloud' isn't going to be able to use an index, so isn't going to be the most performant query"
If you know you're going to be querying on username and project shorthand a lot you're just a handful more lines away from instantiating that as a database function which can take your shorthand apart with string functions and get back to a high-performance query. Functions can return rowsets which you can then further filter with WHERE clauses.
Although in that case I think I'd just take the first and second parameters separately anyhow, since
debug_contribution("sophie", "@unison/cloud")
and
debug_contributions("sophie", "unison", "cloud")
isn't a big enough difference to be worth writing string-parsing code. But do as you like.
If you're using it regularly, you can make it a derived column and index it in most RDBSs. Then it'll work with predicate push-down and you can do other fancy things like store it on alternative partitions to optimize block reads when you're not using it.
The other obvious benefit is that it is no different in semantics than any other column, so there's no advance knowledge necessary for other users vs using a function.