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;