Postgresql – How to get the current row in postgres function

functionspostgresql

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;

Best Answer

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

Related Question