When I use the following stable sql function in my queries, instead of its body directly, the execution is significantly slowed down. From the wiki I read this about scalar functions:
the body contains no aggregate or window function calls, no subqueries, no CTEs, no
FROMclause or references to any table or table-like object, none ofGROUP BY,HAVING,ORDER BY,DISTINCT,LIMIT,OFFSET,UNION,INTERSECT,EXCEPT
Therefore I cannot understand how I could make it inlineable.
CREATE OR REPLACE FUNCTION get_orderitem_status(oiid integer)
RETURNS character varying
LANGUAGE sql
STABLE
AS
$$
SELECT COALESCE(
(SELECT ois."statusId"
FROM "OrderItemStatus" ois
JOIN "OrderItem" oi ON oi."id" = oiid
AND ois."orderItemId" = oi."id"
AND ois."createdAt" > oi."updatedAt"
ORDER BY ois."createdAt" DESC LIMIT 1),
(SELECT cs."statusId"
FROM "CategoryStatus" cs
JOIN "OrderItem" oi ON oi."id" = oiid
JOIN "Item" i ON i."id" = oi."itemId"
JOIN "Status" sta ON sta."id" = cs."statusId"
WHERE i."categoryId" = cs."categoryId"
ORDER BY sta."sequence" ASC LIMIT 1)
);
$$;
From this question, I realize that it is easier to make a table function inlineable, rather than a scalar function. But what could be a table function suitable for this task?
Here is the final query where I will use it, and where by replacing the function with its body I get the result in a third of the time:
SELECT id, get_orderitem_status(id)
FROM "OrderItem"
As the author of the cited question wrote:
I know I can just rewrite my query without the function but it is an important design goal to have these types of abstractions be available as building blocks. I am willing to take a small perf hit for that but not a giant one because the function cannot be inlined.
