Re: converting Sybase RULE -> postgreSQL

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 Berkus
Aglio Database Solutions
San Francisco