PL/pgSQL触发器内的select语句在使用WHERE NEW时返回null。

huangapple go评论62阅读模式
英文:

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:
PL/pgSQL触发器内的select语句在使用WHERE NEW时返回null。
Database2 log_table_received that is a copy of log_table that executes
the trigger whenever new values are inserted;
PL/pgSQL触发器内的select语句在使用WHERE NEW时返回null。

答案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 = &#39;INSERT&#39;) THEN
    added_column := NEW.&quot;COLUMN_NAME&quot;;
    target_table_name := NEW.&quot;TABLE_NAME&quot;;
  END IF;
 /*
  * --------------- --CTE to find Columns of table &quot;Target_table_name&quot; 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 &gt; 0
    AND NOT pg_attribute.attisdropped
    AND pg_namespace.nspname = &#39;items&#39;
    AND pg_class.relname = &#39;trace&#39;
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 &#39;ALTER TABLE items.&#39; || LOWER(target_table_name)|| &#39; ADD COLUMN IF NOT EXISTS &#39; || LOWER(added_column) || &#39; VARCHAR(50)&#39;;
    else
    	old_column := added_column || &#39;already exists ! &#39;;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;


create TRIGGER update_table_log_received_trigger
AFTER INSERT
ON items.&quot;TABLE_LOG_RECEIVED&quot;
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.

huangapple
  • 本文由 发表于 2023年2月8日 17:17:32
  • 转载请务必保留本文链接:https://go.coder-hub.com/75383524.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定