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

Question

I'm creating a trigger that triggers ON INSERT to 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();

I also tried using a temp table:

CREATE TEMP TABLE temp_table AS 
    SELECT column_name 
    FROM information_schema."columns"
    WHERE table_schema = 'items' 
      AND table_name = LOWER(target_table_name)
      AND column_name = LOWER(added_column);
 
  -- Check if the column exists in the target table
  SELECT column_name INTO old_column
  FROM temp_table;

The returned exception:

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). 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 i'm stuck at the first problem to add the column.

Answer 1

So you want to automatically synchronize object definitions in a second database whenever somebody runs DDL in the first database.

The only conceivable solution would be to write an event trigger that fires whenever DDL is executed, connects to the other database and performs a similar operation there. You will have to write the event trigger in C. In the function, you get the internal parse tree of the DDL statement and can compose a textual statement from that. Not impossible, but not trivial either.

Don't think you can get that to work reliably with logical replication, if that's what you want. There will be race conditions all over.

I personally would try hard to find a different, simpler solution for the underlying problem.

Answer 2

Fixed; Question should have been :

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

References: https://stackoverflow.com/questions/12597465/how-to-add-column-if-not-exists-on-postgresql

https://stackoverflow.com/questions/20582500/how-to-check-if-a-table-exists-in-a-given-schema/24089729#24089729

https://stackoverflow.com/questions/20194806/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.

EDIT

this is how my script looks now,for anyone to use if the ever needed in the future.

Tl;dr : using IF EXISTS replaces the need to lookup wether anything exists obviously enough, the ispk is self explanatory and the data_type check up is for data coming from firebird databases,

CREATE OR REPLACE FUNCTION insert_table_log_received()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
DECLARE
added_column TEXT;
target_table_name TEXT;
old_column varchar;
old_table varchar;
data_type varchar;
query varchar;
begin
    data_type := '';
added_column := Lower(NEW."column_name");
target_table_name := Lower(NEW."table_name"); 
  
CASE trim(Lower(new."data_type")) 
   WHEN '7' THEN 
   data_type := 'smallint';
   WHEN '8' THEN 
   data_type := 'INTEGER';
   WHEN '10' THEN 
   data_type := 'FLOAT';
   WHEN '12' THEN 
   data_type := 'DATE';
   WHEN '13' THEN 
   data_type := 'TIME';
   WHEN '14' THEN 
   data_type := 'CHAR';
   WHEN '16' THEN 
   data_type := 'BIGINT';
   WHEN '27' THEN 
   data_type := 'DOUBLE';
   WHEN '35' THEN 
   data_type := 'TIMESTAMP';     
   WHEN '37' THEN 
   data_type := 'VARCHAR';
   WHEN 'character varying' THEN 
   data_type := 'VARCHAR';
   WHEN '261' THEN 
   data_type := 'BLOB';
   ELSE 
   data_type := trim(Lower(new."data_type"));
END CASE;
 
        -- Create the table if not exists
if(new."ispk") then
            if(Lower(data_type)=='varchar')then -- case to test whether it's varchar to give it the length property
                    query := 'Create TABLE IF  NOT EXISTS items.' || target_table_name || ' ( ' || added_column || ' ' || data_type || '( ' || new.data_length || ') PRIMARY KEY NOT NULL);';
                else
                    query := 'Create TABLE IF NOT EXISTS items.' || target_table_name || ' ( ' || added_column || ' ' || data_type || ' PRIMARY KEY NOT NULL);';
            end if;
else
            if(Lower(data_type)='varchar')then -- case to test whether it's varchar to give it the length property
                    query := 'Create TABLE IF NOT EXISTS items.' || target_table_name || ' ( ' || added_column || ' ' || data_type || '( ' || new.data_length || '));';
                else
                    query := 'Create TABLE IF NOT EXISTS items.' || target_table_name || ' ( ' || added_column || ' ' || data_type || ' );';
            end if;
end if;
        EXECUTE query;
if(new."ispk") then
                -- Add the new column to the target table
            if(Lower(data_type)='varchar')then  -- case to test whether it's varchar to give it the length property
                    query := 'ALTER TABLE items.' || target_table_name || ' ADD COLUMN IF NOT EXISTS ' || added_column || ' ' || data_type || '(' || new.data_length || ') PRIMARY KEY NOT NULL;';
                else
                    query := 'ALTER TABLE items.' || target_table_name || ' ADD COLUMN IF NOT EXISTS ' || added_column || ' ' || data_type || ' PRIMARY KEY NOT NULL;';
            end if;
else
            if(Lower(data_type)='varchar')then  -- case to test whether it's varchar to give it the length property
                    query := 'ALTER TABLE items.' || target_table_name || ' ADD COLUMN IF NOT EXISTS ' || added_column || ' ' || data_type || '(' || new.data_length || ');';
                else
                    query := 'ALTER TABLE items.' || target_table_name || ' ADD COLUMN IF NOT EXISTS ' || added_column || ' ' || data_type || ' ;';
            end if;
end if;
        EXECUTE query;
      RETURN NEW;
end ;
$function$
;