postgresql函数和触发器在另一个表中插入记录

qv7cva1a 于 2021-07-24 发布在 Java

关注(0)|答案(1)|浏览(427)

我需要帮助来完成以下要求：
[十] Profile 带列的表 id , username , ...
[十] Comment 带列的表 id , content , ...
[十] CommentReference 带列的表 id , profile_id , comment_id , ...
创建新注解时，在插入之前：
[]检查是否 NEW.content 引用用户名，比如 @someusername []检查配置文件表中是否存在每个引用
[]对于存在的引用，请插入 CommentReferences 简介和评论
目前，我有以下代码：
ps:下面的代码有错误，我需要帮助来修复它。我用的是postgres版本12。

CREATE FUNCTION create_comment_usernames_references()
RETURNS trigger AS $$
DECLARE usernames TEXT[];
DECLARE username TEXT;
DECLARE profile_id TEXT; -- profile_id is of type uuid, is it correct to use TEXT here?
BEGIN
  -- verify if there are usernames in the comment.content with the username regex
  SELECT DISTINCT(
    regexp_matches(
      NEW.content,
      '@(([a-z0-9]*((?<=[a-z0-9])[-|_|\.](?=[a-z0-9]))[a-z0-9]*)*|[a-z0-9]*)',
      'g'
    )
  )[1]
  INTO usernames;

  FOREACH username IN ARRAY usernames LOOP
    SELECT (SELECT id FROM "public"."Profile" WHERE "username" = username) INTO profile_id
    INSERT INTO "public"."CommentReference" (comment_id, profile_id) VALUES (NEW.id, profile_id);
  END LOOP;

  -- return nothing
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER create_comment_usernames_references_trigger
  BEFORE INSERT OR UPDATE ON "public"."Comment"
  FOR EACH ROW
  EXECUTE PROCEDURE create_comment_usernames_references();

sql postgresql postgresql-12

来源：https://stackoverflow.com/questions/62758980/postgres-sql-function-and-trigger-to-insert-record-in-another-table

1条答案

按热度按时间

z8dt9xmd1#

我自己解决。
最终代码：

CREATE OR REPLACE FUNCTION find_profile_ids_by_usernames_in_text(_value TEXT)
    RETURNS TABLE (profile_id   uuid) AS
$func$
BEGIN
    RETURN QUERY
    SELECT
        id
    FROM
        "public"."Profile"
    WHERE
        username IN (
            SELECT DISTINCT(
            regexp_matches(
                _value,
                '@(([a-z0-9]*((?<=[a-z0-9])[-|_|\.](?=[a-z0-9]))[a-z0-9]*)*|[a-z0-9]*)',
                'g')
            )[1]
        );
END
$func$  LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION create_comment_reference(_comment_id UUID, _content TEXT)
RETURNS integer AS $$
DECLARE
    row RECORD;
BEGIN
    FOR row IN
       SELECT * FROM find_profile_ids_by_usernames_in_text(_content)
    LOOP
        INSERT INTO
            "public"."CommentReference" (comment_id, profile_id)
        VALUES
            (_comment_id, row.profile_id)
        ON CONFLICT DO NOTHING;
    END LOOP;
    RETURN 1;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION create_comment_reference_trigger_func()
RETURNS trigger AS $$
DECLARE someval integer;
BEGIN
  select * from create_comment_reference(NEW.id, NEW.content) into someval;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER create_comment_usernames_references_trigger
  AFTER INSERT OR UPDATE ON "public"."Comment"
  FOR EACH ROW
  EXECUTE PROCEDURE create_comment_reference_trigger_func();

赞(0）回复(0）举报 2021-07-24

我来回答

postgresql函数和触发器在另一个表中插入记录

1条答案

相关问题

热门标签

最新问答