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?

• Akina: I have the following usage of COALESCE This is not SQL query. Convert it. Use SELECT COALESCE((subquery1), (subquery2)).

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.

• Thorsten Schöning: oms_rec.meter and clt_rec.captured_at are only available in the outer SELECT, consequently trying a CTE fails for me. My sub select is pretty much a function call with 2 values of the result set as arguments. Are you sure this should work with a CTE? I can't seem to reference the required "arguments".
• Laurenz Albe: Sorry, that must have been a brain fart. Edited with a better idea.