Content-Type: text/plain;
  charset="iso-8859-1"
From: Josh Berkus <josh@agliodbs.com>
Reply-To: josh@agliodbs.com
Organization: Aglio Database Solutions
To: Charles Hauser <chauser@duke.edu>
Subject: Re: converting Sybase RULE -> postgreSQL
Date: Mon, 30 Sep 2002 10:27:48 -0700
References: <1033405365.19271.39.camel@pandorina.biology.duke.edu>
In-Reply-To: <1033405365.19271.39.camel@pandorina.biology.duke.edu>
Cc: pgsql-sql@postgresql.org
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200209301027.48693.josh@agliodbs.com>


Charles,

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

> RULES:
>=20
> 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..
>=20
> 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 wou=
ld=20
be a CONTSTRAINT.     See the documentation on CREATE TABLE  or ALTER TABLE=
=20
to cover constraints.

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

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

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

Yes.   Not exactly equivalent, but functionally equivalent, especially as o=
f=20
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=20=20
>  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 las=
t=20
two functions simply set defaults for two columns.

--=20
-Josh Berkus
 Aglio Database Solutions
 San Francisco