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;