I have a table where I have a column called
event varchar(255) and
user. An event column is a set of strings which tells me in what state was the user when the event was received.
I want to count how many events of each type I have per user.
So I’d like to create a function which would receive as a parameter an event value and then go over each row in the table and if the current row event equals the parameter it will return 1 else 0. The problem is don’t how to get the reference to the current row in a postgres function. This is my code so far:
CREATE OR REPLACE FUNCTION decode_event (eventVar varchar(255)) RETURNS integer AS $result$ BEGIN if event like eventVar then return 1; else return 0; END IF; END; $result$ LANGUAGE plpgsql;
Then I would run a query like the this:
SELECT user, SUM(decode_event('start')) AS start, SUM(decode_event('end')) AS end FROM stats GROUP BY user;
You don’t really need a function for that, you can use conditional aggregation:
SELECT "user", count(*) filter (where event = 'start') AS start, count(*) filter (where event = 'end') AS end FROM stats GROUP BY "user";
You can pass a complete row to a function when you declare a parameter with the type of the table:
CREATE OR REPLACE FUNCTION decode_event (p_row stats, p_name varchar(255)) RETURNS integer AS $result$ BEGIN if p_row.event like eventVar then return 1; else return 0; END IF; END; $result$ LANGUAGE plpgsql;
Then you can use it like this:
SELECT "user", sum(decode_event(stats, 'start')) AS start, sum(decode_event(stats, 'end')) AS end FROM stats GROUP BY "user";
Note how the table alias is passed to the function. If you give the table a different alias, e.g.
from stats as s, you need to pass that alias:
Seems to me that your function could be designed as a scalar function that receives two parameters and returns an integer. Then your query applies that function over the rows and sums the results.
But you could probably do the whole thing in a single query if you wanted to with a common table expression.