pgsql trigger jsonb issue

[copper hedge]

Project ID: N/A

[cold nymph]

CREATE OR REPLACE FUNCTION notify_on_friend_request() RETURNS TRIGGER AS $$
DECLARE
    notification_payload JSONB;
BEGIN
    notification_payload = jsonb_build_object(
        'request_id', NEW.id,
        'from_user', (
            SELECT jsonb_build_object(
                'id', u.id,
                'username', u.username
            )
            FROM "user" u
            WHERE u.id = NEW.from_user
        ),
        'to_user', (
            SELECT jsonb_build_object(
                'id', u.id,
                'username', u.username
            )
            FROM "user" u
            WHERE u.id = NEW.to_user
        ),
        'status', NEW.status, -- Include the 'status' column
        'updated_at', NEW.updated_at
    );
    PERFORM pg_notify('friend_requests', notification_payload::text);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

anyone know if those selects are correct?

[idle robin]

where are you running this query?

[cold nymph]

This is function that is being triggered by trigger that runs on each insert to friend_requests table

this is my migration file,here you can see im creating trigger for table friend_requests calling this trigger that long name,and function in this case is what trigger calles upon INSERT into said table

honestly it worked before but when i reverted to the old code it no longer works

issue is that NEW which should be returning said inserted row is returning null and since this is something i do in free time,im kinda stuck on this for 2 days already ofc with my limited time for personal programming

[idle robin]

wish i knew the problem, but SQL isn't something I'm too familiar with either

[cold nymph]

if you dont mind perhaps tagging someone that could help me ?

@spark vigil any clue?

[spark vigil]

Unfortunately this isn't an issue with Railway. This server is for Railway help, not code help.

[cold nymph]

understandable

should i therefore close this thread?

[spark vigil]

I'm not going to ping anyone in to help, you'll have to wait for someone to stumble on this thread with the answer or visit another server that provides code help