PostgreSQL – Using COALESCE with PERFORM/EXECUTE

postgresql

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?

Best Answer

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.

Related Solutions

Postgresql – Setting up a Postgresql Partition with foreign key

First, I would suggest reading this: http://ledgersmbdev.blogspot.com/2012/08/postgresql-or-modelling-part-3-table.html because although it doesn't cover partitioning, you are doing partitioning using table inheritance and this will cover a lot of details.

In general there are three ways you can handle this. The first is to use specialized key tables (managed with triggers) and reference these tables in your fkey constraints. This is often the simplest approach but it has significant costs in some cases.

The second possibility is you can partition your joining table by the foreign key. This can lead to major complexity problems though and so aside from rare cases, it is my least-favored approach.

A final possibility is you can write your own constraint triggers to manage foreign key enforcement.

Which approach you choose will be dependent on the complexity of joining information, particularly transitively joining information. The second approach allows you to do things entirely declaratively at a significant (possibly major) complexity cost, while the first and third move away from declarative approaches, meaning you may also want to use pgTAP to run test cases on your triggers.....

None of these solutions are without significant complexity. You may want to rethink whether partitioning is the best way to go here and whether partial indexes and other approaches will get you where you need to go.

Postgresql – How to define alias in an ARRAY_AGG expression

You are forming an ad-hoc row type (effectively an anonymous record) with this expression:

(media_files.position, media_files.token, media_files.title)

in your aggregate function call:

ARRAY_AGG((media_files.position, media_files.token, media_files.title)
          ORDER BY media_files.position) AS media_files

Arrays types can only be built upon well-known types. Your option is to announce such a type to the system and cast the record to it before forming the array. Create a well-known composite type:

CREATE TYPE my_type AS (
  position int    -- data type?
 ,token    text
 ,title    text
 )

I am guessing data types for lack of information here. Fill in your actual types.

Creating a table has the same effect: It announces a well known composite type to the system indirectly, as well. For this reason, you can (ab-)use a temporary table to register a composite type for the duration of the session:

CREATE TEMP TABLE my_type AS (
  position int    -- data type?
 ,token    text
 ,title    text
 )

Either way, you can then cast your record:

ARRAY_AGG((media_files.position, media_files.token, media_files.title)::my_type
          ORDER BY media_files.position) AS media_files

Then you can reference elements of the (now well-known) type by name:

SELECT media_files[1].position, media_files[1].token
FROM  (
     ...
   ,ARRAY_AGG((media_files.position, media_files.token, media_files.title)::my_type
          ORDER BY media_files.position) AS media_files
   ...
   FROM ....
   GROUP BY ...
  ) sub;

Now, Postgres can use these names for building a JSON value. Voilá.

Best Answer

Related Solutions

Postgresql – Setting up a Postgresql Partition with foreign key

Postgresql – How to define alias in an ARRAY_AGG expression

Related Question