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.

This is table that has the updated tables from Database1

This is the table that i insert into to log the changes in Database2

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.

• Moifek Maiza: i have that figured, i am also using talend and activeMq to transfer my Data, thus i created the TABLE_LOG table to transfer the new structure, for example : i added sync column to DB1.table1 and i executed my talend job > which executed my trigger the current one i have posted, and DB2.table1 has been altered successfully, the sync column was added my problem is that the other columns are not being detected and thus passing my if condition and throwing an exception.

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$
;