PostgreSQL – Resolving ‘Column Does Not Exist’ Error When Accessing Inner Type Field in ORDER BY

composite-typesdatabase-designorder-bypostgresql

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?

Best Answer

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?

What is the difference between LATERAL and a subquery in PostgreSQL?

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:

Difference between language sql and language plpgsql in PostgreSQL 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.

Related Solutions

Postgresql – How to create an Sql table from SELECT with a multiple row DISTINCT constraint

distinct is NOT a function. It always operates on all columns of the result.

The difference between select distinct (foo), bar and select distinct foo, bar is the same as the difference between select (foo), bar and select foo, bar. The parenthesis is just "noise".

When you write select (foo,bar) you are actually creating an anonymous record type in Postgres which results in a single column that has two attributes - which is not what you actually want.

As you are using Postgres, you can use the (proprietary) operator DISTINCT ON which - in contrast to the standard DISTINCT - does operate on a sub-set of the columns.

You have to specify an ORDER BY in that case to define which of the rows to take if there is more than one with the same combination of (field1, field2).

CREATE TABLE new_name 
AS 
SELECT DISTINCT ON (table.field1, table.field2), 
       table.field1, 
       table.field2, 
       .....
FROM ...
WHERE ...
ORDER BY ..

If you want to stick to ANSI SQL you will need a window function for this:

create table new_name
as
select column1, column2, column3, column4, column5, column6
from (
   select column1, column2, column3, column4, column5, column6, 
          row_number() over (partition by column1, column2 order by ...) as rn 
   from the_table
   where ...
) t
where rn = 1;

For a large table, DISTINCT ON is probably faster than the solution with the window function.

SQL ORDER BY – Implementations in Subqueries

You're going to have to make your application not put the ORDER BY inside the subquery (maybe it has an option to not use a needless subquery in the first place). As you've already discovered, this syntax is not supported in SQL Server without TOP. And with TOP, unless you want to leave some rows out, using TOP 100 PERCENT is going to render the ORDER BY optimized away anyway.

And in Oracle and PostGres, just because the syntax is supported, does not mean it is obeyed. And just because you observe it as being obeyed in some scenario, does not mean that it will continue to be obeyed as new versions come out or with subtle changes to your data, statistics, the query itself, or the environment.

I can assure you that, without a doubt, if you want a guarantee about order, you need to put the ORDER BY on the outermost query. This should be a doctrine you hold close no matter what platform you're using.

You are asking for a link that officially states that something is not supported. This is like looking in your car owner's manual for an official statement that your car cannot fly.