PL/pgSQL dynamic query in stored function to return any table's column names

Question

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?

score 0 · Accepted Answer · answered Jun 13 '22 at 23:06

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.

Thx Belayer. I do indeed have an `instead of` trigger for the view. But was interested in creating it dynamically, though as you say, perhaps a bit too ambitious and better to stick with static sql. The first issue I think I am having is creating a dynamic `execute` with `column_name` and `information_schema` keywords. — akarich73, Jun 14 '22 at 12:10

PL/pgSQL dynamic query in stored function to return any table's column names

1 Answers1