I am trying to write a stored function with a dynamic query that returns all column names from a table that can then be used to create a dynamic query for a joined view trigger function. But struggling to create a stored function with a dynamic query returning column_name from information_schema.
Here is the SQL query I was hoping to convert to a stored function passing the table_name and table_schema as function parameters:
select
column_name
from
information_schema.columns
where
table_name = 'projects' -- to be replaced by parameter
and table_schema = 'public'; -- to be replaced by parameter
I (think I now) understand the basics of needing to use Execute and Format for neatness, but only got a result with passing a table name. This post had a good example of passing a table name: Refactor a PL/pgSQL function to return the output of various SELECT queries
The idea would be to dynamically get the columns then process into a function based on this scratch dynamic query...
DO $$
DECLARE
item varchar;
column_name varchar default 'name';
table_name varchar default 'projects';
temp_string varchar default '';
begin
FOR item IN execute format('SELECT %I FROM %I',column_name,table_name)
loop
temp_string := temp_string || ',NEW.' || item;
END LOOP;
RAISE NOTICE '%', temp_string;
END$$;
And ultimately into the trigger function for views based on a table with a foreign key join. I.e. so the INSERT and UPDATE code is dynamically created for any parent table of a view with a join:
RETURNS trigger
LANGUAGE plpgsql
AS $function$
BEGIN
IF TG_OP = 'INSERT' THEN
INSERT INTO projects VALUES(NEW.id,NEW.name);
RETURN NEW;
ELSIF TG_OP = 'UPDATE' THEN
UPDATE projects SET id=NEW.id, name=NEW.name WHERE id=OLD.id;
RETURN NEW;
ELSIF TG_OP = 'DELETE' THEN
DELETE FROM projects WHERE id=OLD.id;
RETURN NULL;
END IF;
RETURN NEW;
END;
$function$
And finally work out how to deal with foreign key columns.
End result is the parent table can be updated via the view in QGIS. Is this even possible?
I am not exactly sure I understand what you are after but I think parent table can be updated via the view indicates the goal. If so you are headed in the wrong direction entirely and none of what you are seeking is needed. What you want is an instead of
trigger on the view(s). The fiddle here demonstrates an instead of trigger on a view generated with a join, typically these are not cannot normally be updated.
Your idea to dynamically get the columns then process ... and ultimately into the trigger function for views seems extremely ambitious. A better approach may be to build a template for the trigger and associated functions then make the necessary specific column changes. Your trigger(s) must
exist well before any DML action on the views.