postgresql - 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!

标签： postgresqlpostgresql-9.6

Answer 1

让您的函数接受您需要的字段，而不是整个表。

SELECT *, overnight(duration,departure) FROM table_a;

CREATE FUNCTION overnight(duration,departure)
RETURNS boolean AS
SELECT CASE WHEN ... THEN TRUE ELSE FALSE END;
$$;