Thread: stored procedures
One of our DBAs is asking about pg's support for stored procedures. He says he can't find any docs about it. As I understand it, what he wants is to be able store a series of sql statements that would be executed when called. He doesn't want to have to go to an external language like perl or tcl to do this, and says this sort of thing is standard procedure on Oracle, SQLServer, etc. He sent me the definition shown below, about what SPs are. Does PG support this? Can someone give me a link to some docs that I can send to him? Thanks. Petre Scheie --snip-- Stored procedures assist in achieving a consistent implementation of logic across applications. SPs have SQL statements and program logic. Each application needing to perform that task can then simply execute the stored procedure. Coding business logic into a single stored procedure also offers a single point of control for ensuring that business rules are correctly enforced.Stored procedures can also improve performance. Many tasks are implemented as a series of SQL statements. Conditional logic applied to the results of the first SQL statements determines which subsequent SQL statements are executed. SQL statements and conditional logic are written into a stored procedure, they become part of a single execution plan on the server.
On Wed, 12 Mar 2003 15:45:21 -0600 Petre Scheie <petre.scheie@nextelpartners.com> wrote: > One of our DBAs is asking about pg's support for stored procedures. He > says he can't find any docs about it. he must not have looked very hard: http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=plpgsql.html -- Richard Welty rwelty@averillpark.net Averill Park Networking 518-573-7592 Unix, Linux, IP Network Engineering, Security
Petre Scheie wrote: > One of our DBAs is asking about pg's support for stored procedures. He > says he can't find any docs about it. As I understand it, what he wants > is to be able store a series of sql statements that would be executed > when called. He doesn't want to have to go to an external language like > perl or tcl to do this, and says this sort of thing is standard > procedure on Oracle, SQLServer, etc. He sent me the definition shown > below, about what SPs are. Does PG support this? Can someone give me a > link to some docs that I can send to him? Thanks. > PostgreSQL has stored functions, more similar to Oracle's PL/SQL than MSSQL's "stored procedures". Function in Postgres can be written in PL/pgSQL (similar to PL/SQL), PL/Perl, PL/Tcl, PL/Python, PL/sh, PL/R (similar to S-Plus statistical language), and I think there might be a PL/Java available somewhere. See: http://www.us.postgresql.org/users-lounge/docs/7.3/postgres/programmer-pl.html http://webmail.postgresql.org/~petere/plsh.html http://www.joeconway.com/plr/ HTH, Joe
> One of our DBAs is asking about pg's support for stored procedures. He > says he can't find any docs about it. Your DBA obviously can't be bothered to look very hard. The question is almost insulting. ~Berend Tober
I think the dba's doubt's about pg's stored procedures sprang partly from our inability to get to www.postgresql.org yesterday (the page kept coming up blank, even for me). I didn't have any doubt about pg supporting them, but I'm a unix admin, not a dba, so I couldn't intelligently answer his questions. I'm just the point person in the company for pg because I was the one who brought up the idea of using it. The good news is that everyone company who has looked into it has been amazed by what we can do with it. Working with Open Source requires a bit of a different mindset than with proprietary software. It is perhaps similar to the situation where people who grow up in totalitarian countries, then move to democratic states, but then often feel lost because they don't have 'big brother' telling them what to do and how all the time. ;-) Thanks for all the answers and help! Petre -------------------------------------------------- Your DBA obviously can't be bothered to look very hard. The question is almost insulting. ~Berend Tober ---------------------------------------------------- pl-pgsql see the manual: programmer.pdf ----------------------------------------------------- PostgreSQL has stored functions, more similar to Oracle's PL/SQL than MSSQL's "stored procedures". Function in Postgres can be written in PL/pgSQL (similar to PL/SQL), PL/Perl, PL/Tcl, PL/Python, PL/sh, PL/R (similar to S-Plus statistical language), and I think there might be a PL/Java available somewhere. See: http://www.us.postgresql.org/users-lounge/docs/7.3/postgres/programmer-pl.html http://webmail.postgresql.org/~petere/plsh.html http://www.joeconway.com/plr/ HTH, Joe ---------------------------------------------------------------- he must not have looked very hard: http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=plpgsql.html -- Richard Welty ---------------------------------------------------------------