5 ms·

While this is really cool, can anyone tell me if it has become any easier to write stored procedures/functions? Last time I tried writing stored procedures I w

by adamors 13y ago

While this is really cool, can anyone tell me if it has become any easier to write stored procedures/functions? Last time I tried writing stored procedures I went into a deep depression from the lack of a proper IDE/editor/debugger/tester/anything. Is there any process out there besides time consuming trial and error? Or does everyone who writes these knows them by heart and I should just stick to programming languages?

T-R 13y ago
I've not gotten a chance to play around with it yet, but there's plv8x (https://github.com/clkao/plv8x https://github.com/clkao/plv8x), for managing plv8 functions.
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 ago
  Augh, 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 ago
    The 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 ago
      I 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 ago
        Compared 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 ago
        I've found performance pretty decent when using hstore with indexes. You should read about what's coming in hstore 2, also.
        ericlavigne 13y ago
        Do 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 ago
      This 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!
jeltz 13y ago
There are plenty of tools (including editor support, debuggers, testing frameworks, call tree tracers) around stored procedures but I cannot vouch for their quality since I do not use them myself. I mostly do my coding by heart and my testing in the applications I write.
beliu 13y ago
Has anyone tried out python in postgres? (http://www.postgresql.org/docs/9.0/static/plpython.html http://www.postgresql.org/docs/9.0/static/plpython.html). In the past, I've tried to avoid stored procs for the reasons mentioned above, but have not yet had the chance to try out python and would be interested in hearing about people's experiences with it.
- dhruvbhatia 13y ago
  I have, it's great to be able to write DB functions in Python. Only downside is that Amazon RDS does not currently support this extension!
- asnyder 13y ago
  Pretty good, you can even access local python libraries for direct use in your db functions. Only negative is that you have to re-compile your functions to update the library references.
- oneweekwonder 13y ago
  I have not spend any time with it but https://github.com/petere/plpydbapi https://github.com/petere/plpydbapi looks promising to me.
asnyder 13y ago
I've been writing PostgreSQL stored procedures a.k.a functions for almost 10 years. They're great, and have only gotten better. I regularly switch between SQL, plpgsql, and python depending on what's best for the job. Also, plpgsql allows for some really nice functionality with relative ease. Honestly I don't understand how anyone can do more than a basic query in the command line for PostgreSQL. I've been using EMS's PostgreSQL Manager (www.sqlmanager.net) for years. Has auto-complete, even from local aliases. Named, numbered, parameters. Provides base templates for your BEGIN and END, and lots of other goodies. I could work without it, but it would be much more time consuming. I do however, practice most things in the SQL Editor first. Though it does have good built in analysis, sometimes it's nice to just try different things before committing them to a function. Edit: Honestly, I wish they had better screenshots, but this should give you an idea: Functions: http://www.sqlmanager.net/en/products/postgresql/manager/screenshots/1569 http://www.sqlmanager.net/en/products/postgresql/manager/scr... Debugging: http://www.sqlmanager.net/en/products/postgresql/manager/screenshots/1570 http://www.sqlmanager.net/en/products/postgresql/manager/scr... Triggers: http://www.sqlmanager.net/products/postgresql/manager/screenshots/1567 http://www.sqlmanager.net/products/postgresql/manager/screen... Query Builder: (used this much more when I was younger) http://www.sqlmanager.net/en/products/postgresql/manager/screenshots/1586 http://www.sqlmanager.net/en/products/postgresql/manager/scr... Full list: (though don't know how current) http://www.sqlmanager.net/en/products/postgresql/manager/screenshots http://www.sqlmanager.net/en/products/postgresql/manager/scr...
- ibotty 13y ago
  psql also has auto-complete. honestly i really like psql. have a look at its manpage for how much you can customize it to your needs!
  - asnyder 13y ago
    IMO psql is nice for basic things, but when you're writing hundreds of functions it's nice to be able to navigate and manage/edit things a bit more quickly.
    - 13y ago

by adamors 13y ago

T-R 13y ago

I've not gotten a chance to play around with it yet, but there's plv8x (https://github.com/clkao/plv8x https://github.com/clkao/plv8x), for managing plv8 functions.

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 ago
Augh, 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 ago
  The 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 ago
    I 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 ago
      Compared 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 ago
      I've found performance pretty decent when using hstore with indexes. You should read about what's coming in hstore 2, also.
      - ericlavigne 13y ago
        Do 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 ago
    This 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!

jeltz 13y ago

There are plenty of tools (including editor support, debuggers, testing frameworks, call tree tracers) around stored procedures but I cannot vouch for their quality since I do not use them myself. I mostly do my coding by heart and my testing in the applications I write.

beliu 13y ago

Has anyone tried out python in postgres? (http://www.postgresql.org/docs/9.0/static/plpython.html http://www.postgresql.org/docs/9.0/static/plpython.html). In the past, I've tried to avoid stored procs for the reasons mentioned above, but have not yet had the chance to try out python and would be interested in hearing about people's experiences with it.

dhruvbhatia 13y ago
I have, it's great to be able to write DB functions in Python. Only downside is that Amazon RDS does not currently support this extension!
asnyder 13y ago
Pretty good, you can even access local python libraries for direct use in your db functions. Only negative is that you have to re-compile your functions to update the library references.
oneweekwonder 13y ago
I have not spend any time with it but https://github.com/petere/plpydbapi https://github.com/petere/plpydbapi looks promising to me.

asnyder 13y ago

I've been writing PostgreSQL stored procedures a.k.a functions for almost 10 years. They're great, and have only gotten better. I regularly switch between SQL, plpgsql, and python depending on what's best for the job. Also, plpgsql allows for some really nice functionality with relative ease. Honestly I don't understand how anyone can do more than a basic query in the command line for PostgreSQL. I've been using EMS's PostgreSQL Manager (www.sqlmanager.net) for years. Has auto-complete, even from local aliases. Named, numbered, parameters. Provides base templates for your BEGIN and END, and lots of other goodies. I could work without it, but it would be much more time consuming. I do however, practice most things in the SQL Editor first. Though it does have good built in analysis, sometimes it's nice to just try different things before committing them to a function. Edit: Honestly, I wish they had better screenshots, but this should give you an idea: Functions: http://www.sqlmanager.net/en/products/postgresql/manager/screenshots/1569 http://www.sqlmanager.net/en/products/postgresql/manager/scr... Debugging: http://www.sqlmanager.net/en/products/postgresql/manager/screenshots/1570 http://www.sqlmanager.net/en/products/postgresql/manager/scr... Triggers: http://www.sqlmanager.net/products/postgresql/manager/screenshots/1567 http://www.sqlmanager.net/products/postgresql/manager/screen... Query Builder: (used this much more when I was younger) http://www.sqlmanager.net/en/products/postgresql/manager/screenshots/1586 http://www.sqlmanager.net/en/products/postgresql/manager/scr... Full list: (though don't know how current) http://www.sqlmanager.net/en/products/postgresql/manager/screenshots http://www.sqlmanager.net/en/products/postgresql/manager/scr...

ibotty 13y ago
psql also has auto-complete. honestly i really like psql. have a look at its manpage for how much you can customize it to your needs!
- asnyder 13y ago
  IMO psql is nice for basic things, but when you're writing hundreds of functions it's nice to be able to navigate and manage/edit things a bit more quickly.
  - 13y ago