How to get the current row in postgres function?

Question

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;

Answer 1

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: decode_event(s, ...)

• Yos: this solves my problem, but just out of curiosity, is there a way to refer to the current row in a function?
• user1822: @Yos: see my edit

Answer 2

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.