Thread: views and categorized tables
Hi, I'm new in database world, but I know postgres for a long time ago. Now I'm trying to focusing on developing a database using postgresql and postgis extension. I have several question that needs help from all of you. 1. I understand about a view, the lack for me is I cannot edit a view table, is it possible to create a view on top of a table, so I can view the table by joining to another table but I can edit the table also? 2. Can i categorized tables by its category? so when I open the database using my database client I will see "a folder inside a folder" like view, not all the tables listed in my database client, but the category will be listed, and when I open the category then I see the tables. Hope that make sense, and please be patient with me. Cheers, Zery
1) You can attach RULES to a view in order to make it updatable. Consider as an alternative putting INSERT/UPDATE code into FUNCTIONs 2) You can introduce one level of hierarchy into the database by placing tables into SCHEMAs. Make sure to "SET search_path" so that all schemas are listed. You will probably want to externally enforce a "no duplicate names" policy if you do this since the database only checks for duplicate names within a SCHEMA Look at the documentation for (RULE, FUNCTION, SCHEMA) to get more details on what each of these features offer. David J. -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Zery Sent: Saturday, February 26, 2011 10:05 AM To: pgsql-general@postgresql.org Subject: [GENERAL] views and categorized tables Hi, I'm new in database world, but I know postgres for a long time ago. Now I'm trying to focusing on developing a database using postgresql and postgis extension. I have several question that needs help from all of you. 1. I understand about a view, the lack for me is I cannot edit a view table, is it possible to create a view on top of a table, so I can view the table by joining to another table but I can edit the table also? 2. Can i categorized tables by its category? so when I open the database using my database client I will see "a folder inside a folder" like view, not all the tables listed in my database client, but the category will be listed, and when I open the category then I see the tables. Hope that make sense, and please be patient with me. Cheers, Zery -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
On 26/02/2011 15:04, Zery wrote: > Hi, > > I'm new in database world, but I know postgres for a long time ago. Now > I'm trying to focusing on developing a database using postgresql and > postgis extension. I have several question that needs help from all of you. > > 1. I understand about a view, the lack for me is I cannot edit a view > table, is it possible to create a view on top of a table, so I can view > the table by joining to another table but I can edit the table also? You can't edit views in PostgreSQL - they are read-only. You can get a similar effect using rules, but I gather they can be difficult to get right - I've never tried it myself. What exactly are you trying to do? From the above, it sounds a bit as if you might be restricting access to a table; if that's the case, you can do it better using permissions. Views, in my experience, are more often used for simplifying complex queries (among other things). If you tell us more about what you want to do, it will help people to come up with suggestions. > 2. Can i categorized tables by its category? so when I open the database > using my database client I will see "a folder inside a folder" like > view, not all the tables listed in my database client, but the category > will be listed, and when I open the category then I see the tables. No, there's no concept of a table category in PostgreSQL. Perhaps schemas may do what you want? - They effectively give you databases within databases.... though they group together many other types of objects - views, functions, types, etc etc - not just tables. Again, what exactly are you trying to do? Ray. -- Raymond O'Donnell :: Galway :: Ireland rod@iol.ie