![]() This mechanism is how we make the view handle INSERT, UPDATE, and DELETE like a table. Here’s what the general format will look like, which is actually very similar to what was in Part 2:įor a view, we need an INSTEAD OF triger, where on a table we use a BEFORE or AFTER trigger. Note that we sprinkled optional RAISE NOTICE lines in the code so the code flow can be tracked. We’ll do this by adding an INSTEAD OF trigger function on the view. However, we’ll need to make the view “editable” so that we interact with the data by manipulating the JSON. We can update people now by manipulating people_raw and enemy_association. Now we want a people view that returns the enemies list in the characteristics column, so let’s make that:Īnd we can select from the view and get the data we’re after: Alternatively, we specified the person.id values. In the enemy_association insert, we had to specify the column list in order for the enemy_association.id column to get default values. The new people view will gather the data for the enemies key from enemy_association and merge it into the characteristics column. This new table will have the same definition as the people table had before, complete with a characteristics jsonb column that will contain the same JSON data as before except enemies. ![]() We’ll convert people into a view, keep the same data stored in enemy_association as before, and create a new table called people_raw. ![]() In the previous article we had two tables: people and enemy_association. There is a minor penalty for reads of the view equal to the SELECT statement that makes up the view with joins and functions to construct the jsonb columns on the fly.There is only a minor penalty for changing rows through the view due to the triggers deconstructing the JSON and calling the relevant INSERT, UPDATE, or DELETE statements.There is no additional speed penalty for insertion into the backing-store tables (beyond the normal penalty for constraint checking, index updating, etc.).All views and tables can safely be read or written.The data is only in one table, not duplicated as in the previous form.All of the relationships are fully managed by the database itself using traditional foreign-key constraints.Now we’re going to see what it will take to make a read/write view, with the view showing the data constructed as jsonb columns, but with the database backing the view across at least two tables. In part 2 we briefly demonstrated a view that partially deconstructs JSON but noted that views are read-only without some additional work. The other table is used as an associative table and houses only the references and related metadata, complete with foreign-key constraints in place. One table holds the data we had before, including some JSON data, but with all of the references missing. In this post, we show how to create a read/write view that exposes relational data as JSON data (mixed with other JSON data), with the view being a thin layer on top of two tables maintained by triggers. This provides performance and data integrity but at a speed penalty of insertion speed as well as some on-disk data duplication. ![]() In the second post of this series, we demonstrated how to use triggers and constraints to make one read/write table with JSON data and an automatically-maintained read-only table for high-speed joins and to attach the foreign-key constraints to. The name “get” seems a little generic, and maybe we could use different lookup names for the input type, although only integer and string values are permitted.In the first post of this series, we explained why you absolutely need to use jsonb columns in the PostgreSQL tables right now, and mildly chided you if you aren’t already. I’m still not sure about the lookup names, especially the last set.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |