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, ...)

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.