Currently, the CTE patch assumes (without checking) that the output rowtype of a recursive WITH item is the same as the output rowtype determined by inspecting its non-recursive term. Unfortunately this is not always the case. Consider
WITH RECURSIVE q AS ( SELECT int4_col FROM sometable UNION ALL SELECT int8_col FROM ... something referencing q ... ); The output of this UNION will in fact be int8. However I see no way to determine that without performing parse analysis of the recursive term, and we can't do that without having assigned an output rowtype for q (else we have no idea what to do with the recursive reference to q). So it seems like we have to throw an error for this, and insist that the user explicitly do WITH RECURSIVE q AS ( SELECT int4_col::int8 FROM sometable UNION ALL SELECT int8_col FROM ... something referencing q ... ); Can anyone see a way around that? I'm inclined to go a bit further and have the code assume that the output typmods are all -1, even if some more-specific typmod can be determined from the non-recursive term. Otherwise you'd need to explicitly cast in situations like WITH RECURSIVE q AS ( SELECT varchar_10_col FROM sometable UNION ALL SELECT varchar_12_col FROM ... something referencing q ... ); On the other hand this rule would lose typmod information even in cases where both UNION arms emit the same typmod, so maybe it's debatable. Comments? Another point is that the patch assumes that the non-recursive term must be the left child of the topmost UNION operator. In SQL2008, 7.13 syntax rule 2.g.i.3 appears to allow either child to be the non-recursive term. However, rule 2.g.v.3 seems to say that a recursive query is considered "expandable" only if the left child is the non-recursive term, and in any case it'd be a bit odd to write a recursive query the other way; it seems more likely to be a mistake than intentional. Is everybody happy with making this restriction? If we don't make it, then we also have some issues with the output column names of the UNION not necessarily being what we derive from inspecting just the non-recursive term. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers