Re: stored procedures: sybase -> postgreSQL ?

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(at)duke(dot)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