Stored Procedures in Python for PostgreSQL

Question

Hello, we are still pretty new to Postgres and came from Microsoft Sql Server.

We are wanting to write some stored procedures now. Well, after struggling to get something more complicated than a hello world to work in pl/pgsql, we decided it's better if we are going to learn a new language we might as well learn Python because we got the same query working in it in about 15 minutes(note, none of us actually know python).

So I have some questions about it in comparison to pl/psql.

1. Is pl/Pythonu slower than pl/pgsql?
2. Is there any kind of "good" reference for how to write good stored procedures using it? Five short pages in the Postgres documentation doesn't really tell us enough.
3. What about query preparation? Should it always be used?
4. If we use the SD and GD arrays for a lot of query plans, will it ever get too full or have a negative impact on the server? Will it automatically delete old values if it gets too full?
5. Is there any hope of it becoming a trusted language?

Also, our stored procedure usage is extremely light. Right now we only have 4, but we are still trying to convert little bits of code over from Sql Server specific syntax(such as variables, which can't be used in Postgres outside of stored procedures)

Answer 1

1. Depends on what operations you're doing.
2. Well, combine that with a general Python documentation, and that's about what you have.
3. No. Again, depends on what you're doing. If you're only going to run a query once, no point in preparing it separately.
4. If you are using persistent connections, it might. But they get cleared out whenever a connection is closed.
5. Not likely. Sandboxing is broken in Python and AFAIK nobody is really interested in fixing it. I heard someone say that python-on-parrot may be the most viable way, once we have pl/parrot (which we don't yet).

Bottom line though - if your stored procedures are going to do database work, use pl/pgsql. Only use pl/python if you are going to do non-database stuff, such as talking to external libraries.