2

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?

  • Show us the CREATE TABLE a_table; code. – ypercubeᵀᴹ Sep 13 '16 at 6:45
  • @ypercubeᵀᴹ right, I make it easier to read but it is wrong. The subtype comes from a sproc, I'll update the question. – mat_boy Sep 13 '16 at 6:49
  • 2
    You can't reference an alias like that in the order by clause. You need to wrap that into a derived table. I also think you are over complicating things by using types like that. Your queries would be a lot easier if you would just store the information in a normalized way as regular columns - but that might just be me, – a_horse_with_no_name Sep 13 '16 at 7:30
  • I agree with @a_horse_with_no_name. It sounds like a target for some more design thinking. Furthermore, you could define that function as a SQL language one, removing the PL/pgSQL clutter. – dezso Sep 13 '16 at 7:36
  • 2
    @mat_boy if nothing else, it helps readability, reduces the number of context switches when executing queries, furthermore in some cases it might render the function inlineable: wiki.postgresql.org/wiki/Inlining_of_SQL_functions – dezso Sep 13 '16 at 15:17
3

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.

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.