Thread: stored procedures

stored procedures

From
Petre Scheie
Date:
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.


Re: stored procedures

From
Richard Welty
Date:
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

Re: stored procedures

From
Joe Conway
Date:
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


Re: stored procedures

From
Date:
> 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




Re: stored procedures

From
Petre Scheie
Date:
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
---------------------------------------------------------------