From: Josh Berkus <josh@agliodbs.com>
To: Charles Hauser <chauser@duke.edu>
Cc: pgsql-sql@postgresql.org
Subject: Re: converting Sybase RULE -> postgreSQL
Date: Mon, 30 Sep 2002 10:27:48 -0700
Message-ID: <200209301027.48693.josh@agliodbs.com> (raw)
In-Reply-To: <1033405365.19271.39.camel@pandorina.biology.duke.edu>
References: <1033405365.19271.39.camel@pandorina.biology.duke.edu>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: josh@agliodbs.com, chauser@duke.edu
Subject: Re: converting Sybase RULE -> postgreSQL
In-Reply-To: <200209301027.48693.josh@agliodbs.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox