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.

Laurenz Albe · Accepted Answer · 2023-02-09 17:26:44Z

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.

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. — Moifek Maiza
– Moifek Maiza, Commented Feb 13, 2023 at 11:36

Moifek Maiza · Accepted Answer · 2023-04-13 08:12:35Z

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

Stack Exchange Network

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

2 Answers 2

Hot Network Questions

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

2 Answers 2

Related

Hot Network Questions