4 ms·
Just this week I had my own go at plpgsql again a few years after my last attempts. I have come away with the same conclusions I did last time "oh wow, this is
by skyebook 13y ago
Just this week I had my own go at plpgsql again a few years after my last attempts. I have come away with the same conclusions I did last time "oh wow, this is really cool and fast and great; oh bummer, this is taking way too many of my own cycles to get something working".
The pg docs unfortunately weren't much beyond selecting some data. Trying to write any logic is something that left me struggling (in my case, I needed a hash table that could store a key value pair of numbers).
From my own experience a few years ago, however, go look at the stored procedures in PostGIS. There are many, many of them methodically written and you could probably learn most of what there is to know from detailed inspection.
- drob 13y agoAugh, I needed a hashmap in plpgsql last week! It's the worst. Best I could come up with was an awful hack in which I left stuff in a table with columns (key, val, invocation_code), where the third is a UUID specific to the invocation of the function. (Disgusting! I'm not proud of this.) I'd pay (one upvote) for a blog post with a better way to do this. If one doesn't exist, this might call for a postgres extension.
- ericlavigne 13y agoThe extension you want is hstore. It allows you to create variables and columns of type hstore which is a (String -> String) hashmap. If you actually needed something more like (String -> Integer) or (String -> Decimal), then you'll just need to cast any values on their way out of the hstore. http://www.postgresql.org/docs/9.1/static/hstore.html http://www.postgresql.org/docs/9.1/static/hstore.html
- drob 13y agoI guess we could do it with hstore. I wonder what sort of performance I'll get on it for a hashmap workload. Hmm...
- ericlavigne 13y agoCompared with adding multiple rows to a table to simulate one local variable? I expect readability and performance will both be greatly improved, but... it's hard to offer a good recommendation without knowing what sort of calculation you're trying to perform.
- NatW 13y agoI've found performance pretty decent when using hstore with indexes. You should read about what's coming in hstore 2, also.
- ericlavigne 13y agoDo you have a good description of hstore 2? A google search found a few references that there is an hstore 2, and that it might be related to PG 9.4, and that it might allow the values to be non-string, and it might allow nesting... nothing very clear.
- skyebook 13y agoThis was actually the last thing I was looking at before throwing in the towel and moving back to the app server in the interest of getting things done. I'll have to give it another go some time, thanks!