![]() (To work around this, create a domain over the composite type, and apply the desired constraints as CHECK constraints of the domain.) Note however an important restriction of the current implementation: since no constraints are associated with a composite type, the constraints shown in the table definition do not apply to values of the composite type outside the table. Then the same inventory_item composite type shown above would come into being as a byproduct, and could be used just as above. Supplier_id integer REFERENCES suppliers, Whenever you create a table, a composite type is also automatically created, with the same name as the table, to represent the table's row type. SELECT price_extension(item, 10) FROM on_hand INSERT INTO on_hand VALUES (ROW('fuzzy dice', 42, 1.99), 1000) ĬREATE FUNCTION price_extension(inventory_item, integer) RETURNS numeric Having defined the types, we can use them to create tables: Note that the AS keyword is essential without it, the system will think a different kind of CREATE TYPE command is meant, and you will get odd syntax errors. The syntax is comparable to CREATE TABLE, except that only field names and types can be specified no constraints (such as NOT NULL) can presently be included. ![]() It might also be useful to enhance the INSERTs to handle conflicts, especially if some of the columns are associated with a parent table with multiple child rows.Here are two simple examples of defining composite types: In addition to matching by column name, it might also be desirable to address foreign key reference columns between a view's base tables. SELECT STRING_AGG(inserts.statement, ' ' || CHR(13) || CHR(10))ĬREATE OR REPLACE FUNCTION %I.%I() RETURNS TRIGGER LANGUAGE plpgsql AS GROUP BY tables.table_schema, tables.table_name) ON tables.table_oid = ta.attrelid AND ta.attnum > 0 STRING_AGG(FORMAT('NEW.%I', ta.attname), ', ' ORDER BY ta.attnum)) AS statement STRING_AGG(FORMAT('%I', ta.attname), ', ' ORDER BY ta.attnum), INSERTS AS (SELECT FORMAT($$INSERT INTO %I.%I (%s) VALUES (%s)$$, ON t.relnamespace = nt.oid AND t.relkind IN ('r', 'f', 'p') ON dv.objid = dt.objid AND dv.refobjid dt.refobjid WITH tables AS (SELECT DISTINCT t.oid AS table_oid, nt.nspname AS table_schema, t.relname AS table_name WHERE v.oid = gen_view_to_table_trig_func.source_view Ĭreated_function_name := COALESCE(function_name, view_name || '_iotf') (This approach can be expanded to handle DELETE and UPDATE as well.) CREATE OR REPLACE FUNCTION gen_view_to_table_trig_func(source_view regclass, function_name text DEFAULT NULL) RETURNS text The following demonstates a function that queries the system catalogs to generate a trigger function definition to populate a view's base tables. A better approach is to create dedicated functions for each view. This approach would not be very performant. A function that worked for the general case would need to query the system catalogs to dynamically create insert statements for each row inserted into a view. While it's possible to create a trigger function that could be applied to multiple views to perform the described inserts, it's not advisable. I hope I have explained my question clearly. the value NEW.dob, would get inserted in the dob column on usr_static. In other words, without explicitly mapping the columns in the INSERT statement to the values on NEW as in the above function. The tricky part here is to somehow dynamically map the values on NEW to the correct table. Here the % would get replaced with the name of the view ( usr in this case). So my question is, if it is somehow possible to do something like: INSERT INTO %_static VALUES (NEW.*) RETURNING id INTO NEW.id ![]() So I was wondering if there is a way to create one trigger that I can apply to multiple views? This is a very common pattern in the application that I'm working on. My trigger function would then look like: CREATE FUNCTION my_trigger_func() RETURNS trigger ![]() Now I am trying to create a trigger function for this view that intercepts an INSERT into the view and splits it up into 2 inserts: 1 for the usr_static table and 1 for the usr_version table: INSERT INTO usr (5, '', 'John', 'male') LEFT JOIN usr_version ON usr_static.id = usr_version.id SELECT usr_static.id, usr_static.dob, usr_version.name, usr_version.gender. Let's say we have two tables: CREATE TABLE usr_static ( My question is best illustrated with an example:
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |