Date: Tue, 10 Sep 2002 09:49:08 -0500
From: "Ross J. Reedstrom" <reedstrm@rice.edu>
To: Charles Hauser <chauser@duke.edu>
Cc: pgsql-sql@postgresql.org
Subject: Re: stored procedures: sybase -> postgreSQL ?
Message-ID: <20020910144908.GD1023@rice.edu>
Mail-Followup-To: "Ross J. Reedstrom" <reedstrm@ece.rice.edu>,
	Charles Hauser <chauser@duke.edu>, pgsql-sql@postgresql.org
References: <1031595353.774.55.camel@pandorina.biology.duke.edu>
	<np2qnu4ti7kq0uadqd38o87pm0c1snrspb@4ax.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <np2qnu4ti7kq0uadqd38o87pm0c1snrspb@4ax.com>
User-Agent: Mutt/1.3.27i

On Mon, Sep 09, 2002 at 11:02:27PM +0200, Reinoud van Leeuwen wrote:
> On Mon, 9 Sep 2002 18:16:07 +0000 (UTC), chauser@duke.edu (Charles
> Hauser) wrote:
> 
> >I am trying to port a Sybase table create script to one usable for
> >postgreSQL.
> >
> >(note I am not a DBA)
> >
> >In particular I am not well versed on how to use/recode the stored
> >procedures such as that in the example below.
> >
> >ALTER TABLE DnaFragment
> >        ADD PRIMARY KEY (dna_fragment_id)
> >go
> > 
> > exec sp_primarykey DnaFragment,
> >       dna_fragment_id
> >go
> > 
> > exec sp_bindrule DnaFragment_type_rule, 'DnaFragment.type'
> > exec sp_bindefault Set_To_Current_Date,
> >'DnaFragment.date_last_modified'
> > exec sp_bindefault Set_to_False, 'DnaFragment.is_obsolete'
> >go

As Reinoud hinted at, these aren't really stored procedures: they're
setting up defaults and constraints, which PostgreSQL does in a more
SQL standard manner.  This specific example would probably translate
like so - note that you don't show the table schema or rule definitions,
so I have to guess at column types and there probably are other columns.

CREATE TABLE DnaFragment ( 
    dna_fragment_id INT PRIMARY KEY,
    type INT CHECK ([an expression equivalent to DnaFragment_type_rule]),
    is_obsolete BOOL DEFAULT 'f',
    date_last_modified DATE DEFAULT current_date)
    

depending on what DnaFragment_type_rule does, it might just be a foreign
key reference (change CHECK (expression) to REFERENCES table (column) )

You might want to upgrade the date to a timestamp field, to get finer
grained information on modifications.

If you're not interested in learning a fair amount of DB theory, using
some sort of automated tool may in fact be the answer. On the other
hand, knowing _exactly_ how the data is structured/stored can lead to a
better understanding of what sort of queries are trivial, and what sort
are impossible.

Ross