Обсуждение: Stored Procedures

Greets,

Couple of questions,

    1: does postgres support stored procedures
    2: say a user has a microsoft sql server 7 database
    with ~120 stored procedures, and alot of data, is their
    a script or tool to convert that to a postgres database
    or does it have to be done by hand.


Jeff MacDonald
jeff@hub.org

===================================================================
 So long as the Universe had a beginning, we can suppose it had a
creator, but if the Universe is completly self contained , having
no boundry or edge, it would neither be created nor destroyed
 It would simply be.
===================================================================

On Fri, 5 Nov 1999, Jeff MacDonald wrote:

>     1: does postgres support stored procedures

Yes, quite extensively, and far beyond what SQL 7 offers.  You may want
to take a look at the programmer's manual for the documentation.

>     2: say a user has a microsoft sql server 7 database
>     with ~120 stored procedures, and alot of data, is their
>     a script or tool to convert that to a postgres database
>     or does it have to be done by hand.
>

You can convert the data over with ODBC, but not the stored procedures.
Under SQL 7, stored procedures are essentially SQL batch files and cannot
be used as a term in an expression, whereas under PostgreSQL, you can
create true functions in PL/PgSQL (a procedural language akin to
Oracle's), or as loadable executable modules written in C, C++, Tcl, etc.,
that returns values and objects, and can be used in an expression.

I was shocked recently when we put in SQL 7 in our office (we've been
using PostgreSQL for a while now, but we needed SQL 7 to use with a
commercial retrieval system) and needed to start writing functions as I
was used to under PostgreSQL, and couldn't.  A big win for PostgreSQL!

Brett W. McCoy
                                         http://www.lan2wan.com/~bmccoy
-----------------------------------------------------------------------
There are people so addicted to exaggeration that they can't tell the
truth without lying.

----- Original Message -----
From: Brett W. McCoy <bmccoy@lan2wan.com>
To: Jeff MacDonald <jeff@hub.org>
Cc: <pgsql-general@postgreSQL.org>
Sent: Friday, November 05, 1999 3:52 PM
Subject: Re: [GENERAL] Stored Procedures


> On Fri, 5 Nov 1999, Jeff MacDonald wrote:
>
> > 1: does postgres support stored procedures
>
> Yes, quite extensively, and far beyond what SQL 7 offers.  You may
want
> to take a look at the programmer's manual for the documentation.

I thought we had rather a long debate recently about whether and if
Postgres did/should support stored procedures, and the point was that
it doesn't at the moment?

PL/PgSQL functions are not at all the same thing, although they are
obviously very useful.
Yours,
Moray

----------------------------------------------------------------------
----------------
Moray.McConnachie@computing-services.oxford.ac.uk

> 1: does postgres support stored procedures

Yes, but they can't return datasets in any meaningfull way. They can return
single values only.

> 2: say a user has a microsoft sql server 7 database
> with ~120 stored procedures, and alot of data, is their
> a script or tool to convert that to a postgres database
> or does it have to be done by hand.

You have to rewrite your procedures if they return rows. IMO, this is the
major deficiency of Postgers.

Gene Sokolov.

On Fri, 5 Nov 1999, Moray McConnachie wrote:

> > Yes, quite extensively, and far beyond what SQL 7 offers.  You may
> want
> > to take a look at the programmer's manual for the documentation.
>
> I thought we had rather a long debate recently about whether and if
> Postgres did/should support stored procedures, and the point was that
> it doesn't at the moment?
>
> PL/PgSQL functions are not at all the same thing, although they are
> obviously very useful.

What's the difference between the two, then?  What does a stored procedure
do that a function doesn't?  The PostgreSQL 'CREATE FUNCTION', as it is
described in the documentation, is very similar to the not yet
standardized stored module facility in SQL.

Brett W. McCoy
                                         http://www.lan2wan.com/~bmccoy
-----------------------------------------------------------------------
Commitment, n.:
    Commitment can be illustrated by a breakfast of ham and eggs.
The chicken was involved, the pig was committed.