Virtual Column Only Executing for First Row

Question

I'm trying to used a function to create a virtual column on my table. The function executes correctly on its own, but when I add to my table:

SELECT *, table_a.function FROM table_a;

It only executes the function for the first row of the result and gives the same output for all subsequent rows.

Example Function:

CREATE FUNCTION overnight(table_a)
RETURNS boolean AS
$$
SELECT CASE WHEN (((duration / 60) * 100) + (duration % 60) + departure) >= 2400 THEN TRUE ELSE FALSE END FROM schema.table_a
$$;

How do I ensure the code executes for every row of my table?

Thanks!

Answer 1

You need to reference the parameter in your select and get rid of the FROM clause (which selects from the table not from the row parameter).

To avoid confusion between the table name and the parameter I strongly recommend to use a proper parameter name:

CREATE FUNCTION overnight(p_row table_a)
  RETURNS boolean AS
$$
  SELECT (((p_row.duration / 60) * 100) + (p_row.duration % 60) + p_row.departure) >= 2400;
$$
language sql
stable;

Then you can use it like this:

SELECT *, table_a.function 
FROM table_a;

Online example