pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedconverting Sybase RULE -> postgreSQL
2+ messages / 2 participants
[nested] [flat]
* converting Sybase RULE -> postgreSQL
@ 2002-09-30 17:02 Charles Hauser <chauser@duke.edu>
0 siblings, 1 reply; 2+ messages in thread
From: Charles Hauser @ 2002-09-30 17:02 UTC (permalink / raw)
To: pgsql-sql
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)
go
exec sp_primarykey CloneEnd,
clone_end_id
go
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'
go
regards,
Charles
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: converting Sybase RULE -> postgreSQL
@ 2002-09-30 17:27 Josh Berkus <josh@agliodbs.com>
parent: Charles Hauser <chauser@duke.edu>
0 siblings, 0 replies; 2+ messages in thread
From: Josh Berkus @ 2002-09-30 17:27 UTC (permalink / raw)
To: Charles Hauser <chauser@duke.edu>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2002-09-30 17:27 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-09-30 17:02 converting Sybase RULE -> postgreSQL Charles Hauser <chauser@duke.edu>
2002-09-30 17:27 ` Josh Berkus <josh@agliodbs.com>
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