Database Administration
postgresql database-design order-by composite-types
Updated Fri, 10 Jun 2022 18:07:17 GMT

Column does not exists when accessing inner type field in ORDER BY


In PostgreSQL 9.4 I have two types:

CREATE TYPE a_schema.type_child {  
  an_order smallint
}
CREATE TYPE a_schema.type_parent {
  pluto uuid,
  child type_child
}

Then in a view I have this script:

SELECT 
    something_a,
    something_b,
    ARRAY(SELECT
            ROW (a_uuid,
              a_schema.get_a_type_child(a_type_child_id)
            )::a_schema.type_parent AS tp
          FROM a_schema.a_table
          ORDER BY ((tp).type_child).an_order ASC)
FROM ....

where the stored procedure just does a select as:

CREATE FUNCTION a_schema.get_a_type_child(IN type_id smallint, OUT a_type a_schema.type_child)
  RETURNS a_schema.type_child AS
$BODY$
DECLARE
BEGIN
  SELECT
      ROW (a_order)::a_schema.type_child
  FROM a_schema_another_table
  WHERE at_id = type_id
  LIMIT 1
  INTO a_type;
END;
$BODY$
LANGUAGE plpgsql VOLATILE SECURITY DEFINER COST 1;

The error I get is:

ERROR: column "tp" does not exist

related to the ORDER BY clause.

I know that I could just do

ORDER BY tp

But then I have to define an ordering for the type, which introduces a lot of code.

Is there any way to access the inner type field an_order for ordering purpose?

Solution

Explanation for the error

You get the error message:

ERROR: column "tp" does not exist

because you cannot use output columns in another expression in the ORDER BY clause. You can only reference output column names as a whole. Expressions must be built on input columns. The manual:

Each expression can be the name or ordinal number of an output column (SELECT list item), or it can be an arbitrary expression formed from input-column values.

Bold emphasis mine.

Solution

You would need the row type as input to your query to involve it in expressions in ORDER BY. You can do that with a LATERAL join:

SELECT 
    something_a,
    something_b,
    ARRAY (
      SELECT (a_uuid, my_child)::a_schema.type_parent AS tp
      FROM   a_schema.a_table
      LEFT   JOIN LATERAL (SELECT get_a_type_child(a_type_child_id) AS my_child) t ON true
      ORDER  BY (t.my_child).an_order
      )
FROM ....

Why LEFT JOIN LATERAL () ON true?

Asides

The solution is merely a proof of concept. You need intimate knowledge of row and composite type handling to cope with this setup, and it will still be awkward and slow. Your setup is more complicated than necessary to begin with. I would have to type so much to explain everything, it's just not worth it.

I agree with @a_horse: a properly normalized schema will save you a lot of headache.

The function can be simpler, but there is nothing wrong with PL/pgSQL per se. SECURITY DEFINER functions are exempt from function inlining anyway. There are other pros and cons for SQL vs. PL/pgSQL functions:

Note: functions. Your term stored procedure is a bit off, since Postgres does not have true stored procedures. Just functions - doing almost but not quite the same.


External Links

External links referenced by this document:

Linked Articles

Local articles referenced by this article:

References