时间戳

首页 > 解决方案 > PostgreSQL copy to a new table with one extra column. How?

sql - PostgreSQL copy to a new table with one extra column. How?

问题描述

I am trying to make a forum database and in this database I have a trigger. After the Insert it should copy the values of the other table into the new one, but should also make a new column showing the actual registerdate of the user. Here is the trigger code:

Create OR REPLACE FUNCTION insertUser() RETURNS TRIGGER AS $insertUser$

BEGIN 

    INSERT INTO public.freshlyinserted(user_id, username, bday, gender, uemail, pasword)
    VALUES (NEW.user_id, NEW.username, NEW.bday, NEW.gender, NEW.uemail, NEW.pasword, NEW.registerdate);

RETURN NEW;
END;
$insertUser$ LANGUAGE plpgsql;

CREATE TRIGGER moveUserToInserted
AFTER INSERT ON forumusers
FOR EACH ROW EXECUTE PROCEDURE insertUser();

When i try this, it tells me this:

ERROR Record "new" has no field "registerdate" CONTEXT: SQL statement "INSERT INTO public.freshlyinserted(user_id, username, bday, gender, uemail, pasword) VALUES (NEW.user_id, NEW.username, NEW.bday, NEW.gender, NEW.uemail, NEW.pasword, NEW.registerdate)". PL/pgSQL function insertuser() row 2 for SQL statement

The forumuser table doesn't have the "registerdate" column...but I really would like it for the new Table withe the actual date(i dont know how to do it tho). I would be happy, if I get help with this. I will post any more needed code! Btw. Without the registerdate, it works...but then the table would make no sense for me XD

Thanks! :)

标签： sqldatabasepostgresqltriggerspostgresql-triggers

解决方案

只需像这样使用current_date：

Create OR REPLACE FUNCTION insertUser() RETURNS TRIGGER AS $insertUser$

BEGIN 

    INSERT INTO public.freshlyinserted(user_id, username, bday, gender, uemail, pasword)
    VALUES (NEW.user_id, NEW.username, NEW.bday, NEW.gender, NEW.uemail, NEW.pasword, current_date);

RETURN NEW;
END;
$insertUser$ LANGUAGE plpgsql;

CREATE TRIGGER moveUserToInserted
AFTER INSERT ON forumusers
FOR EACH ROW EXECUTE PROCEDURE insertUser();

您还可以使用current_timestamp

在这里您可以看到使用它们时它们会返回或保存什么：https ://dbfiddle.uk/?rdbms=postgres_12&fiddle=57bae2ba351bac477724aa5551c73ee2

推荐阅读