英文:
PL/pgSQL select statement inside trigger returns null using where NEW
问题
我正在创建一个触发器,它会在一个表上执行INSERT操作时触发,我希望记录被插入的表的结构,所以我编写了这个函数:
CREATE OR REPLACE FUNCTION update_table_log_received()
RETURNS TRIGGER AS $$
DECLARE
added_column TEXT;
target_table_name TEXT;
old_column text;
BEGIN
-- 检查是否添加了新列
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 (Lower(added_column) != 'sync') THEN
-- 将新列添加到目标表
EXECUTE 'ALTER TABLE items.' || LOWER(target_table_name) || ' ADD COLUMN ' || LOWER(added_column) || ' VARCHAR(50)';
END IF;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
这个函数的目的是在INSERT操作触发时检查是否添加了新的列,如果是,则将新列添加到目标表。如果你遇到了问题,可能是因为在函数中执行的SELECT语句返回了NULL值。你也尝试过使用"USING NEW",但没有成功。
关于你的问题,可能是由于声明的变量没有从"NEW"记录中获取到值,或者SELECT语句的执行有问题。你可以确保"NEW"记录中的列名和表名是正确的,以及在information_schema中是否能找到匹配的列名。
你的问题的要点是,当Database1中的表结构发生变化时,你希望能够更新Database2中的相同表,不论是添加列还是修改列。目前,你卡在尝试添加新列的问题上。
英文:
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;
答案1
得分: 0
尝试以下语法:
CREATE OR REPLACE FUNCTION update_table_log_received()
RETURNS TRIGGER AS $$
DECLARE
added_column TEXT;
target_table_name TEXT;
old_column text;
BEGIN
-- 检查是否已添加新列
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;
我在我的数据库上尝试过,它可以正常工作。你可以根据需要修改一些细节。
英文:
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.
答案2
得分: 0
已经翻译的部分:
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.
英文:
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论