PL/pgSQL select statement inside trigger returns null using where NEW

Question

i'm creating a trigger that triggers on INSERT on a table, and i wish to log the structure of tables inserted so i wrote this Function

CREATE OR REPLACE FUNCTION update_table_log_received()
RETURNS TRIGGER AS $$
DECLARE
  added_column TEXT;
  target_table_name TEXT;
  old_column text;
BEGIN
  -- Check if a new column has been added
  IF (TG_OP = 'INSERT') THEN
    added_column := NEW."COLUMN_NAME";
    target_table_name := NEW."TABLE_NAME";
  END IF;
    SELECT column_name into old_column
                   FROM information_schema."columns"
                   WHERE table_schema = 'items' 
                   and table_name = LOWER(NEW."TABLE_NAME")
                  and column_name = LOWER(NEW."COLUMN_NAME");
if (coalesce(old_column,'')='' or old_column='' or old_column = added_column) THEN
  -- If a new column has been added
  IF (Lower(added_column) != 'sync') then
    
    -- Add the new column to the target table
        EXECUTE 'ALTER TABLE items.' || LOWER(target_table_name)|| ' ADD COLUMN ' || LOWER(added_column) || ' VARCHAR(50)';
  END IF;
end if;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

executed by this TRIGGER :

CREATE TRIGGER update_table_log_received_trigger
AFTER INSERT
ON items."TABLE_LOG_RECEIVED"
FOR EACH ROW
EXECUTE FUNCTION update_table_log_received();

the returned exception is the following :

! ERROR: the column « x » of the relation « y » already exists Where: instruction SQL « ALTER TABLE items. ADD COLUMN x VARCHAR(50) »

my problem now is that it isn't supposed to pass the If checks (i pasted the code after many alterations i have two if conditions that do the same thing just because), i debugged and logged the statements to note that the select query inside my function returns null apparently. i also tried to use "USING NEW" but i am no expert so i couldn't make it work

is it a problem with the declared variable not being populated from the "NEW" record or am i executing the select statement wrong ?

EDIT : tl;dr for my problem, I would like to update a table in Database2 whenever the same table (that had the same structre) is altered from Database1, be it added column or changed column, at this point iI'm stuck at the first problem to add the column.

I am logging my tables' structures as strings into a new table and syncing that with Database2 to then have the trigger alter the same altered table from Database1, hope this makes more sense now.

Database1 log_table that logs all my tables' structures: Database2 log_table_received that is a copy of log_table that executes the trigger whenever new values are inserted;

Answer 1

Try this syntax:

CREATE OR REPLACE FUNCTION update_table_log_received()
RETURNS TRIGGER AS $$
DECLARE
  added_column TEXT;
  target_table_name TEXT;
  old_column text;
BEGIN
  -- Check if a new column has been added
  IF (TG_OP = 'INSERT') THEN
    added_column := new."column_name";
    target_table_name := new."table_name";
  END IF;

  if not exists(select 1 from information_schema."columns" where table_name = target_table_name and column_name = added_column) 
  then 
      EXECUTE 'ALTER TABLE items.' || LOWER(target_table_name)|| ' ADD COLUMN ' || LOWER(added_column) || ' VARCHAR(50)';
  end if; 
  
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

I tried on my DB this is works. You can change some details yourself.

Answer 2

Fixed; Question should have been :

• How to select tables & table columns inside function in postgresql.

References: How to add column if not exists on PostgreSQL?

How to check if a table exists in a given schema

How to get a list column names and datatypes of a table in PostgreSQL?

Basically information_schema can only be accessed by owner meaning the user or (i) see the result when i query it but it returns FALSE when executed inside a script more details here : https://stackoverflow.com/a/24089729/15170264

Full trigger after fix with CTE to query the pg_catalog also added ADD COLUMN IF NOT EXISTS in my Execute query just to be safe

CREATE OR REPLACE FUNCTION update_table_log_received()
RETURNS TRIGGER AS $$
DECLARE
  added_column TEXT;
  target_table_name TEXT;
  old_column varchar;
    old_table varchar;
BEGIN
  -- Check if a new column has been added
  IF (TG_OP = 'INSERT') THEN
    added_column := NEW."COLUMN_NAME";
    target_table_name := NEW."TABLE_NAME";
  END IF;
 /*
  * --------------- --CTE to find Columns of table "Target_table_name" from pg_catalog
  */

    WITH cte_tables AS (
   SELECT
    pg_attribute.attname AS column_name,
    pg_catalog.format_type(pg_attribute.atttypid, pg_attribute.atttypmod) AS data_type
FROM
    pg_catalog.pg_attribute
INNER JOIN
    pg_catalog.pg_class ON pg_class.oid = pg_attribute.attrelid
INNER JOIN
    pg_catalog.pg_namespace ON pg_namespace.oid = pg_class.relnamespace
WHERE
    pg_attribute.attnum > 0
    AND NOT pg_attribute.attisdropped
    AND pg_namespace.nspname = 'items'
    AND pg_class.relname = 'trace'
ORDER BY
    attnum ASC
)
select column_name into old_column from cte_tables where 
column_name=LOWER(added_column);

 
  if (old_column is null )  then 
    -- Add the new column to the target table
        old_column := added_column;
        EXECUTE 'ALTER TABLE items.' || LOWER(target_table_name)|| ' ADD COLUMN IF NOT EXISTS ' || LOWER(added_column) || ' VARCHAR(50)';
    else
        old_column := added_column || 'already exists ! ';
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;


create TRIGGER update_table_log_received_trigger
AFTER INSERT
ON items."TABLE_LOG_RECEIVED"
FOR EACH ROW
EXECUTE FUNCTION update_table_log_received();

Variable old_column stores the else condition message but i do not return it, would have if it was a simple function.