Обсуждение: converting Sybase RULE -> postgreSQL

Hi,

I am still a novice at this so bear with me.
I am going to try to alter a Sybase TABLE create script (excerpt
below)to postgreSQL.

I suspect some of the script is specific to Sybase and I'll just need to
remove it.  In particular there are numerous stored procedures ("sp_"). 
But first things first. 

RULES:

In the sample below the RULE CloneEnd_type restricts input: the only
data which can be inserted or updated into CloneEnd.type have to be one
of 'BAC_end', 'YAC_end'  etc..

I know postgresql supports RULES but have not used them prior. How would
one cone this for postgresql?


Stored Procedures:

Are FUNCTIONS (postgresql)equivalent to stored procedures (Sybase)?






### Sybase code:  ###
CREATE RULE CloneEnd_type_rule AS @col IN ('BAC_end', 'YAC_end', 'TAC_end', 'EST', 'unknown', 'P1_end', 'plasmid')
go

ALTER TABLE CloneEnd       ADD PRIMARY KEY (clone_end_id)
goexec sp_primarykey CloneEnd,      clone_end_id
goexec sp_bindrule CloneEnd_type_rule, 'CloneEnd.type'exec sp_bindefault Set_To_Current_Date,
'CloneEnd.date_last_modified'execsp_bindefault Set_to_False, 'CloneEnd.is_obsolete'
 
go


regards,

Charles

Charles,

You're correct.  Most of the wierd stuff below is stuff Sybase invented to get
around limitations, and failure to support the SQL standard, in their
product.

> RULES:
>
> In the sample below the RULE CloneEnd_type restricts input: the only
> data which can be inserted or updated into CloneEnd.type have to be one
> of 'BAC_end', 'YAC_end'  etc..
>
> I know postgresql supports RULES but have not used them prior. How would
> one cone this for postgresql?

In Postgres, or in SQL92 for that matter, this would not be a Rule.  It would
be a CONTSTRAINT.     See the documentation on CREATE TABLE  or ALTER TABLE
to cover constraints.

Please also be aware that the particular constraint you mention would be
better implemented through a reference table ("clone_end_types") and a
FORIEGN KEY CONSTRAINT.

Finally, remember that if you use mixed-case table names, you will have to
quote them all the time.

> Stored Procedures:
>
> Are FUNCTIONS (postgresql)equivalent to stored procedures (Sybase)?

Yes.   Not exactly equivalent, but functionally equivalent, especially as of
7.3.

>  ALTER TABLE CloneEnd
>         ADD PRIMARY KEY (clone_end_id)

This is also done with Constraints in Postgres and the SQL spec.

>  exec sp_primarykey CloneEnd,
>        clone_end_id
>  exec sp_bindrule CloneEnd_type_rule, 'CloneEnd.type'
>  exec sp_bindefault Set_To_Current_Date, 'CloneEnd.date_last_modified'
>  exec sp_bindefault Set_to_False, 'CloneEnd.is_obsolete'

All of the above is done through the table definition in Postges.   The last
two functions simply set defaults for two columns.

--
-Josh BerkusAglio Database SolutionsSan Francisco