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.
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.
Outputs The command tag returned by EXECUTE is that of the prepared statement, and not EXECUTE.
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?