Thread: stored procedures: sybase -> postgreSQL ?

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)
goexec sp_primarykey DnaFragment,      dna_fragment_id
goexec 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



regards,

Charles

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

Postgresql and Sybase are a lot different in many ways. Lot of the
Postgresql is clearly borrowed from oracle ways of thinking (mind that
this is not neccessary better or worse, it is different).
When using standard SQL, the differences are not really much.
Postgresql uses that ANSI outer join syntax instead of the Sybase =*
notation.
But when you start using Sybase specific features the differences will
become more clean. Rules and defaults are not defined as seperate
entities, so you have to define them inside the table definitions. The
alter table statement is by the way explained on
http://www.postgresql.org/idocs/index.php?sql-altertable.html.

If you want to convert one database definition to another, you might
want to use a tool like Sybase Powerdesigner. You can read the Sybase
definition, and create a Postgresql definition from there. An
evaluation version (fully functional for a limited time) is
downloadable from the Sybase website

-- 
__________________________________________________
"Nothing is as subjective as reality"
Reinoud van Leeuwen       reinoud.v@n.leeuwen.net
http://www.xs4all.nl/~reinoud

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