1

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?

1
  • 1
    I have the following usage of COALESCE This is not SQL query. Convert it. Use SELECT COALESCE((subquery1), (subquery2)). – Akina Feb 4 at 11:13
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.

2
  • 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". – Thorsten Schöning Feb 4 at 14:14
  • Sorry, that must have been a brain fart. Edited with a better idea. – Laurenz Albe Feb 4 at 14:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.