select statement in postgres function called inside a trigger

Question

I'm trying to develop a notification system for the backend of a social media application/website. For now I'm focusing on status updates. What I'm going to do, is putting a trigger on the postgres table that related to status updates so that every time a new status update is posted, a notification is sent to my code.

So far I have been able to do that. But an extra feature that I like to implement is extracting all of the people who follow the user who posted the status update, so that I can also send them a notification that the person they're following has posted a new status update.

Of course it can be implemented by first receiving the notification for a new status update from postgres, extracting the user id of the person who posted it, and make a query to the database to find out which users follow them.

But I figured it would be more efficient if I don't make the query, and instead, each time postgres wants to send me a notification about a new status update, it also makes a query to find out which users are following the poster of the status update and send that information along with the notification for the new status update.

But I can't figure out how I can make a query in a postgres function that depends on the argument of that function, and then send the result of that query along with the argument as a notification.

this is what I've tried:

create table example (c1 text, c2 text);

create function notif() 
   returns trigger as 
$$ 
begin 
  perform pg_notify('event',row_to_json(new)::text); 
  return new; 
end; 
$$ language plpgsql;

create trigger trig after insert 
   on example 
   for each row execute procedure notif();

And then I listen to the event channel from my code and receive the row that was inserted. But I want to do a select statement based on the new row in my notif() function and send the result with the new row to the listening code.

I'd appreciate any clarification

Thanks

I'd appreciate clarification too: how does the notification work (trigger? NOTIFY?) What do you mean by "send the query result along with the function arguments"? Do you mean "send along with the function result"? You see, some code sketch would be helpful. — Laurenz Albe
– Laurenz Albe, Commented Apr 11, 2019 at 5:54
Simple: pack the query result into the JSON. Where exactly is the problem? — Laurenz Albe
– Laurenz Albe, Commented Apr 11, 2019 at 6:54
well, I don't know how to do that. I can't find anything in the postgres documentations and I couldn't find anything via google too. — massivefermion
– massivefermion, Commented Apr 11, 2019 at 6:55
Also, in the codes I've written I'm not defining any new variable and even the function I create doesn't have any arguments. But somehow magically using return new the row is returned within the notification. How am I supposed to add anything to that? How variables in postgres work? It all seems so magical and nothing is clearly explained in the documentations. — massivefermion
– massivefermion, Commented Apr 11, 2019 at 6:56
You should ask different questions then, like "how to compose a JSON value from NEW and some query result in a trigger function?" The JSON functions are well documented, as is NEW. As it is, the question is unclear and/or too broad. — Laurenz Albe
– Laurenz Albe, Commented Apr 11, 2019 at 7:05

freakish · Accepted Answer · 2019-04-11 12:20:05Z

4

Something like this?

CREATE FUNCTION notif()
RETURNS TRIGGER AS $$
DECLARE
    data JSONB;
    result JSONB;
BEGIN
    SELECT json_agg(tmp)  -- requires Postgres9.3+
    INTO data
    FROM (
        -- your subquery goes here, for example:
        SELECT followers.following_user_id
        FROM followers
        WHERE followers.followed_user_id = NEW.user_id
    ) tmp;

    result := json_build_object('data', data, 'row', row_to_json(NEW));
    PERFORM pg_notify('event', result::TEXT);
    RETURN NEW;
END;
$$ language plpgsql;

Also from comments:

But somehow magically using return new the row is returned within the notification.

You misunderstand things. Return and notification are two different things.

First of all lets deal with return. For AFTER INSERT triggers the return value is totally ignored:

The return value of a row-level trigger fired AFTER or a statement-level trigger fired BEFORE or AFTER is always ignored; it might as well be null.

The return value only matters for BEFORE triggers. In which case you can modify (or even prevent) the row before writing to the table. See this: https://www.postgresql.org/docs/9.2/plpgsql-trigger.html This has nothing to do with notifications.

So what about notifications? Whatever you receive from a notification is what you pass as second argument to pg_notify. All of that is quite well documented: https://www.postgresql.org/docs/9.0/sql-notify.html

edited Apr 11, 2019 at 12:20

answered Apr 11, 2019 at 10:41

freakish

57k12 gold badges141 silver badges181 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

massivefermion Over a year ago

That's great, thanks. Just one thing. I want both NEW and the result of the subquery to be inside the json object that pg_notify sends. How can I encode them to a single variable?

Collectives™ on Stack Overflow

select statement in postgres function called inside a trigger

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related