Greenplum CTE not found - Error [XX000] (allpaths.c:904)

I'm building a PostgreSQL query for a Greenplum DB to "flatten" all composite types in tables into pseudo columns to make sure a legacy system which uses MySQL could understand what's going on. I don't know if this is a reasonable solution, tho.

To accomplish that, I'm using CTEs and I stumbled on this error:

SQL Error [XX000]: ERROR: could not find CTE "aggregated_attributes" (allpaths.c:904)

What is causing this?

It seems to occur in a specific case. I think it could be something related to CTE scope, but I don't know much how they work under the hood.

It works for the following query:

WITH RECURSIVE udt_tree (udt_catalog, udt_name, attribute_name) AS (
SELECT
udt_catalog,
udt_name,
attribute_name
FROM information_schema."attributes"
WHERE data_type = 'USER-DEFINED'
UNION ALL
SELECT
_attributes.udt_catalog,
_attributes.udt_name,
_attributes.attribute_name
FROM
udt_tree,
information_schema."attributes" AS _attributes
WHERE ((_attributes.udt_name, _attributes.udt_catalog)
= (udt_tree.attribute_name, udt_tree.udt_catalog))
),
aggregated_attributes AS (
SELECT
udt_catalog,
udt_name,
array_agg(
CAST(attribute_name AS varchar)
ORDER BY attribute_name) AS attributes_names
FROM udt_tree
GROUP BY
udt_catalog,
udt_name
),
aggregated_tree (udt_catalog, udt_name, attributes_tree) AS (
SELECT
udt_catalog,
udt_name,
attributes_names AS attributes_tree
FROM aggregated_attributes
UNION ALL
SELECT
at.udt_catalog,
at.udt_name,
CASE
WHEN aa.udt_name IS NULL THEN at.attributes_tree
ELSE ARRAY(
SELECT CAST(attribute_name AS text)
FROM (SELECT unnest(at.attributes_tree) AS attribute_name) AS deepest_level
WHERE attribute_name <> aa.udt_name
UNION
SELECT aa.udt_name || '_' || attribute_name
FROM (SELECT unnest(aa.attributes_names) AS attribute_name) AS aggregated_level)
END AS attributes_tree
FROM
aggregated_tree AS at,
aggregated_attributes AS aa
WHERE aa.udt_name = ANY (at.attributes_tree)
)
SELECT
DISTINCT ON (udt_catalog, udt_name)
*
FROM aggregated_tree
ORDER BY
udt_catalog,
udt_name,
attributes_tree DESC;


But I want it to look like this (which does not work). They are pretty similar, except for the type_attributes CTE:

type_attributes

WITH RECURSIVE udt_tree (udt_catalog, udt_name, attribute_name) AS (
SELECT
udt_catalog,
udt_name,
attribute_name
FROM information_schema."attributes"
WHERE data_type = 'USER-DEFINED'
UNION ALL
SELECT
_attributes.udt_catalog,
_attributes.udt_name,
_attributes.attribute_name
FROM
udt_tree,
information_schema."attributes" AS _attributes
WHERE ((_attributes.udt_name, _attributes.udt_catalog)
= (udt_tree.attribute_name, udt_tree.udt_catalog))
),
aggregated_attributes AS (
SELECT
udt_catalog,
udt_name,
array_agg(
CAST(attribute_name AS varchar)
ORDER BY attribute_name) AS attributes_names
FROM udt_tree
GROUP BY
udt_catalog,
udt_name
),
aggregated_tree (udt_catalog, udt_name, attributes_tree) AS (
SELECT
udt_catalog,
udt_name,
attributes_names AS attributes_tree
FROM aggregated_attributes
UNION ALL
SELECT
at.udt_catalog,
at.udt_name,
CASE
WHEN aa.udt_name IS NULL THEN at.attributes_tree
ELSE ARRAY(
SELECT CAST(attribute_name AS text)
FROM (SELECT unnest(at.attributes_tree) AS attribute_name) AS deepest_level
WHERE attribute_name <> aa.udt_name
UNION
SELECT aa.udt_name || '_' || attribute_name
FROM (SELECT unnest(aa.attributes_names) AS attribute_name) AS aggregated_level)
END AS attributes_tree
FROM
aggregated_tree AS at,
aggregated_attributes AS aa
WHERE aa.udt_name = ANY (at.attributes_tree)
),
type_attributes AS (
SELECT
DISTINCT ON (udt_catalog, udt_name)
*
FROM aggregated_tree
ORDER BY
udt_catalog,
udt_name,
attributes_tree DESC
)
SELECT * FROM type_attributes;


To reproduce, create a database named ericsson_voz and then create the tables using this ddl:

ericsson_voz

CREATE TYPE CAMELTDPData AS (
serviceKey varchar,
gsmSCFAddress varchar
);
CREATE TYPE UserRate AS ENUM ('urindneg', 'ur600bps', 'ur1200bps', 'ur2400bps', 'ur3600bps', 'ur4800bps', 'ur7200bps', 'ur8000bps', 'ur9600bps', 'ur14400bps', 'ur16000bps', 'ur19200bps', 'ur32000bps', 'ur38400bps', 'ur48000bps', 'ur56000bps', 'ur64000bps', 'ur38400bps1', 'ur57600bps', 'ur28800bps', 'ur134-5bps', 'ur100bps', 'ur75bps-1200bps', 'ur1200bps-75bps', 'ur50bps', 'ur75bps', 'ur110bps', 'ur150bps', 'ur200bps', 'ur300bps', 'ur12000bps');
CREATE TYPE AsyncSyncIndicator AS ENUM ('syncdata', 'asyncdata');
CREATE TYPE UILayer1Protocol AS ENUM ('v110-x30', 'g711mulaw', 'g711alaw', 'g721-32000bps-i460', 'h221-h242', 'h223-h245', 'nonitu-t', 'v120', 'x31', 'vselp-speech');
CREATE TYPE MultimediaInformation AS (
userRate UserRate,
asyncSyncIndicator AsyncSyncIndicator,
uILayer1Protocol UILayer1Protocol
);
CREATE TYPE TriggerDetectionPoint AS ENUM ('originatingcallattemptauthorized', 'collectedinformation', 'analyzedinformation', 'originatingcallattemptrouteselectfailure', 'originatingcallattemptcalledpartybusy', 'originatingcallattemptcalledpartynotanswer', 'originatingcallattemptcalledpartyanswer', 'originatingcallattemptmid-calleventdetected', 'originatingcallattemptcalldisconnecting', 'originatingcallattemptcallabandon', 'terminatingcallattemptauthorized', 'terminatingcallattemptcalledpartybusy', 'terminatingcallattemptnoanswer', 'terminatingcallattemptanswer', 'terminatingcallattemptmid-calleventdetected', 'terminatingcallattemptcalldisconnect', 'terminatingcallattemptcallabandon', 'terminatingcallattemptcallreanswer', 'terminatingcallattemptcallsuspended', 'terminatingcallattemptcalledpartynotreachable', 'terminatingcallattemptalerting', 'terminatingcallattemptrouteselectfailure', 'originatingcallattemptcalledpartyreanswer', 'originatingcallattemptcallsuspended', 'originatingcallattemptcalledpartynotreachable', 'originatingcallattemptalerting');
CREATE TYPE SCPAddress AS (
pointCodeAndSubSystemNumber varchar,
globalTitle varchar,
globalTitleAndSubSystemNumber varchar
);
CREATE TYPE TriggerData AS (
triggerDetectionPoint TriggerDetectionPoint,
serviceKey varchar,
sCPAddress SCPAddress
);
CREATE TABLE MSOriginating (
bCSMTDPData1 CAMELTDPData
) WITH (APPENDONLY=true, ORIENTATION=column) DISTRIBUTED RANDOMLY;
CREATE TABLE Transit (
multimediaInformation MultimediaInformation
) WITH (APPENDONLY=true, ORIENTATION=column) DISTRIBUTED RANDOMLY;
CREATE TABLE INIncomingCall (
triggerData0 TriggerData
) WITH (APPENDONLY=true, ORIENTATION=column) DISTRIBUTED RANDOMLY;


The expected result is:

udt_catalog udt_name attributes_tree
ericsson_voz multimediainformation asyncsyncindicator,uilayer1protocol,userrate
ericsson_voz scpaddress globaltitle,globaltitleandsubsystemnumber,pointcodeandsubsystemnumber
ericsson_voz triggerdata scpaddress_globaltitle,scpaddress_globaltitleandsubsystemnumber,scpaddress_pointcodeandsubsystemnumber,triggerdetectionpoint


type_attributes

type_attributes

aggregated_attributes

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.