Re: Proposal for SYNONYMS

Jonah H. Harris wrote:
> 
> 
> This email is a preliminary design for the implementation of synonyms in 
> PostgreSQL.  Comments and suggestions are welcomed.
> 
> BACKGROUND
> 
> Synonyms are database objects which can be used in place of their 
> referenced object in SELECT, INSERT, UPDATE, and DELETE SQL statements.
> 
> There are two reasons to use synonyms which include:
> 
> - Abstraction from changes made to the name or location of database objects
> - Alternative naming for another database object
> 
> Similarly, RDBMS support for synonyms exists in Oracle, SQL Server, DB2, 
> SAP DB/MAX DB, and Mimer.
> 
> PROPOSED SQL ADDITIONS
> 
> CREATE SYNONYM qualified_name FOR qualified_name
> DROP SYNONYM qualified_name
> 
> In addition, SYNONYMS do participate in ACLs and support GRANT/REVOKE 
> for table privileges. DROP TABLE and TRUNCATE cannot be used with synonyms.
> 
> DESCRIPTION
> 
> - A synonym can be created for a table, view, or synonym.
> - Synonyms can reference objects in any schema
> 
> RESTRICTIONS
> 
> - A synonym may only be created if the creator has some access privilege 
> on the referenced object.
> - A synonym can only be created for an existing table, view or synonym.
> - A synonym name cannot be the same as the name of any other table, view 
> or synonym which exists in the schema where the synonym is to be created.
> 
> PROPOSED IMPLEMENTATION
> 
> - Introduce a new relkind for synonyms
> - Synonyms only act as pointers to a real object by oid
> - Permission on a synonym does not override the permission on the 
> referenced object
> - Referenced objects becomes dependencies of the synonyms that reference 
> them
> - Synonyms follow PostgreSQL's current search_path behavior
> 
> RUNTIME COST
> 
> - Dependent on database user/administrator
> - In catalog searches which do not reference a synonym, the only cost 
> incurred is that of searching the additional number of synonym objects 
> in the catalog
> - In catalog searches which use a synonym, an additional cost is 
> incurred to reference the real object
> - If no synonyms are created, no additional costs are incurred
> 


hi jonah ...

the main problem i can see here is that it is strictly limited to 
objects stored in pg_class.
however, support for stored procedures would be cool as well. what do 
you suggest for those?
best regards,
    hans


-- 
Cybertec Geschwinde & Schönig GmbH
Schöngrabern 134; A-2020 Hollabrunn
Tel: +43/1/205 10 35 / 340
www.postgresql.at, www.cybertec.at