From: Ross J. Reedstrom <reedstrm@rice.edu>
To: Charles Hauser <chauser@duke.edu>
Cc: pgsql-sql@postgresql.org
Subject: Re: stored procedures: sybase -> postgreSQL ?
Date: Tue, 10 Sep 2002 09:49:08 -0500
Message-ID: <20020910144908.GD1023@rice.edu> (raw)
In-Reply-To: <np2qnu4ti7kq0uadqd38o87pm0c1snrspb@4ax.com>
References: <1031595353.774.55.camel@pandorina.biology.duke.edu>
<np2qnu4ti7kq0uadqd38o87pm0c1snrspb@4ax.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: reedstrm@rice.edu, chauser@duke.edu
Subject: Re: stored procedures: sybase -> postgreSQL ?
In-Reply-To: <20020910144908.GD1023@rice.edu>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox