Can COALESCE be used with PERFORM/EXECUTE in PostgreSQL?

Question

I have the following usage of COALESCE, which works as expected:

[...]coalesce
(
    status_avail_direct.status,
    (   
        SELECT  status
        FROM    status_avail_direct

        WHERE   (status_avail_direct.meter_id    = oms_rec.meter) AND 
                (status_avail_direct.clt_rec_at <= clt_rec.captured_at)

        ORDER BY    status_avail_direct.clt_rec_at DESC
        LIMIT       1
    ),
    (SELECT -1)
)

To make it better readable, I would like to extract and document the embedded SELECT into some outer statement/query or alike. The important thing is that I need values of the currently processed rows for that SELECT to work, e.g. oms_rec.meter and clt_rec.captured_at. From my understanding, because of that a CTE doesn't work, but a prepared statement should exactly fit my needs. So I changed the SQL to the following:

PREPARE status_calc_by_time(timestamp with time zone, int) AS
    SELECT  status
    FROM    status_avail_direct
    
    WHERE   (status_avail_direct.meter_id    = $2) AND 
            (status_avail_direct.clt_rec_at <= $1)
    
    ORDER BY    status_avail_direct.clt_rec_at DESC
    LIMIT       1
;

[...]coalesce
(
    status_avail_direct.status,
    (EXECUTE status_calc_by_time(clt_rec.captured_at, oms_rec.meter)),
    (SELECT -1)
)

But that doesn't work, a syntax error is recognized instead:

[Code: 0, SQL State: 42601] FEHLER: Syntaxfehler bei »status_calc_by_time« Position: 263 [Script position: 1510 - 1550]

From the docs I had the feeling that wherever I can use a query directly, I might be able to use a prepared statement as well:

statement Any SELECT, INSERT, UPDATE, DELETE, or VALUES statement.

https://www.postgresql.org/docs/9.2/sql-prepare.html

Outputs The command tag returned by EXECUTE is that of the prepared statement, and not EXECUTE.

https://www.postgresql.org/docs/9.2/sql-execute.html

Is there something wrong with my syntax I don't see or does executing a prepared statement like that simply doesn't work at all in this context? In the latter case, where can I use EXECUTE most likely/only?

Answer 1

EXECUTE is a PL/pgSQL statement, not an SQL statement, so you cannot use it as subquery inside an SQL statement.

You could write a function status_calc_by_time, then your code would work if you simply remove the EXECUTE.

If you write the function in LANGUAGE sql and make it VOLATILE, it might get inlined by the database, so you have no performance loss at all.