Thread: Relation in tables
Hello all... I am starting in Postgresql... And I have a question: I am developing a DB system to manage products, but the products may be separated by departaments (with its respectives coluns)... Like: CREATE TABLE products( id serial primary key, desc valchar(100), ... ); Okay, but the products is typed by "amount departament" and this departament should not have access to other coluns like "values, Money, etc...". The "finances departament" may modify the data into products table, but this departament should not have access to coluns like "amounts, etc...". I' ve tried to create the products table with INHERITS but its not right... look: CREATE TABLE prod_amounts ( amount_min numeric, amount_cur numeric, amount_max numeric, ... ) INHERITS products; CREATE TABLE prod_values ( buy_value money, sen_value money, ... ) INHERITS products; Okay, but the problem is: I can INSERT a prod_amounts normaly and automaticaly the products table will be filled, but when i try to modify the data in prod_amounts (references in products) there is no data.... I think its not right ( I am sure :/ ). How Can I do it??? How Can I References Between Prod_amounts and Prod_Values automaticaly? remembering the Amounts departament may not access the Values departament data and the Values departament may not access the amounts data... And the products will be registred (typed) in Amount departament.... Thanks for all.
You may possibly solve the problem with the inheritted tables with the RULE-System of pgsql. But this seems oversized to me. You could rather create several tables, each with its matching rights/privileges and 'connect' them via an 1:1 relation. The 'real' way such thing is normally done is to write stored procedures which are accessible (Executable) only by those departments that may. If you give these procs SECURITY DEFINER, you can restrict access to the actual tables to only the procedure's owner > -----Original Message----- > From: lucas@presserv.org [mailto:lucas@presserv.org] > Sent: Mittwoch, 16. Februar 2005 17:43 > To: pgsql-sql@postgresql.org > Subject: [SQL] Relation in tables > > > Hello all... > I am starting in Postgresql... > And I have a question: > I am developing a DB system to manage products, but the > products may be > separated by departaments (with its respectives coluns)... Like: > > CREATE TABLE products( > id serial primary key, > desc valchar(100), > ... > ); > > Okay, but the products is typed by "amount departament" and > this departament > should not have access to other coluns like "values, Money, etc...". > The "finances departament" may modify the data into products > table, but this > departament should not have access to coluns like "amounts, etc...". > > I' ve tried to create the products table with INHERITS but > its not right... > look: > > CREATE TABLE prod_amounts ( > amount_min numeric, > amount_cur numeric, > amount_max numeric, > ... > ) INHERITS products; > > CREATE TABLE prod_values ( > buy_value money, > sen_value money, > ... > ) INHERITS products; > > Okay, but the problem is: I can INSERT a prod_amounts normaly > and automaticaly > the products table will be filled, but when i try to modify > the data in > prod_amounts (references in products) there is no data.... > I think its not right ( I am sure :/ ). > How Can I do it??? > How Can I References Between Prod_amounts and Prod_Values > automaticaly? > remembering the Amounts departament may not access the Values > departament data > and the Values departament may not access the amounts data... > And the products > will be registred (typed) in Amount departament.... > > Thanks for all. > > ---------------------------(end of > broadcast)--------------------------- > TIP 9: the planner will ignore your desire to choose an index > scan if your > joining column's datatypes do not match >
> Hello all... > I am starting in Postgresql... > And I have a question: > I am developing a DB system to manage products, but the products may be > separated by departaments (with its respectives coluns)... Like: > > CREATE TABLE products( > id serial primary key, > desc valchar(100), > ... > ); > > Okay, but the products is typed by "amount departament" and this > departament > should not have access to other coluns like "values, Money, etc...". > The "finances departament" may modify the data into products table, but > this > departament should not have access to coluns like "amounts, etc...". > > I' ve tried to create the products table with INHERITS but its not > right... > look: Use a view per department, which show/hide the columns according to your liking. Give each department a schema and put everything related to it inside for cleanliness. Use UPDATE triggers on the views, which in fact write to the products table, so that the departments can only update the columns you like. You can even make some columns readable but not writeable, by raising an exception if a modification is attempted on that column. If you want to reuse your code between departments, you will want all the views to have the same columns, so make them return NULL for the fields that they cannot see. Finally don't forget to make the products table inaccessible the departments.
On Wed, 16 Feb 2005 19:56:25 +0100, PFC wrote > [snip] Use UPDATE triggers on the > views, which in fact write to the products table [snip] You can DO that!?! Are you saying that a client can DELETE or INSERT or UPDATE a view and through a trigger you can make this happen? Way cool. Can you provide a simple example? And all this time I thought that you couldn't write to a view. Kind Regards, Keith
> And all this time I thought that you couldn't write to a view. You can't.But you can make it seem so you can. You can create an ON UPDATE/INSERT trigger on a view which intercepts the UPDATE/INSERT to the view (which would otherwise fail) and do whatever you want with it, including doing the operation on the real table. Search for "postgresql materialized views" for some examples.
PFC <lists@boutiquenumerique.com> writes:
> You can create an ON UPDATE/INSERT trigger on a view which intercepts the
> UPDATE/INSERT to the view (which would otherwise fail) and do whatever you
> want with it, including doing the operation on the real table.
This might work for INSERT but I really doubt it'll work for UPDATE or
DELETE.
You may be able to make it work with rules, though there are lots of
pitfalls in that approach.
regards, tom lane
> > Use a view per department, which show/hide the columns according to your > liking. Give each department a schema and put everything related to it > inside for cleanliness. Use UPDATE triggers on the views, which in fact > write to the products table, so that the departments can only update the > columns you like. You can even make some columns readable but not > writeable, by raising an exception if a modification is attempted on that > column. > > If you want to reuse your code between departments, you will want all the > views to have the same columns, so make them return NULL for the fields > that they cannot see. > > Finally don't forget to make the products table inaccessible the > departments. > Okay, I will use Triggers to manage the insert and update table and viewers to select records, but need I use more than one table (with inherits)? Or I just use product table with the ALL departaments coluns and check the perms into Triggers and Viewers??? Thanks