Postgres SQL function and trigger to insert record in another table

Question

I need help to complete the following requirements:

[x] Profile table with columns id, username, ...
[x] Comment table with columns id, content, ...
[x] CommentReference table with columns id, profile_id, comment_id, ...

When a new comment is created, before inserting:

[ ] Check if NEW.content have references to usernames, like @someusername
[ ] Check if each reference exists in the profile table
[ ] For references that exist, insert into CommentReferences the profile and comment

For now, what I have is the following code:

PS: the following code has errors, I need help to fix it. I'm using postgres version 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();

@kofemann I'm running it inside hasura console, the error output isn't very helpful and I'm not an SQL expert, it just says there is some error near or at `INSERT` — pedrobern, Jul 06 '20 at 15:24
The first guess will be to check that **SELECT** before insert have returned some values, for example **IF FOUND THEN....*** — kofemann, Jul 06 '20 at 15:32
@kofemann I think there is some problem with the `profile _id` it's in the format of a table, so I think I need to extract the `id` from the `profile_id` before inserting but not sure how to do this. Also, I need to add the IF statement of course. — pedrobern, Jul 06 '20 at 15:38

score 0 · Accepted Answer · answered Jul 06 '20 at 21:55

Solve it myself.

Final code:

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();

Postgres SQL function and trigger to insert record in another table

1 Answers1