function format_type not supported on DBOS/SPU

Question

I am getting subject error when trying to run below query in Neteeza - Aginity

(SELECT 'TABLE1' AS TABLE_NAME, 'CHANGE_BY_CD' AS COL,(SELECT DATA_TYPE 
FROM information_schema.columns WHERE table_name = 'TABLE1'
AND COLUMN_NAME = 'CHANGE_BY_CD') AS COL_LEN, MAX(LENGTH(CHANGE_BY_CD)) AS MAX_LEN, CHANGE_BY_CD::CHARACTER VARYING(2000) AS VAL FROM SCHEMA1.A_TABLE1 
WHERE CHANGE_BY_CD IS NOT NULL GROUP BY CHANGE_BY_CD ORDER BY MAX_LEN DESC , CHANGE_BY_CD DESC LIMIT 1) 

Error

ERROR:  function format_type not supported on DBOS/SPU

This error pops up after adding below part to the query

(SELECT DATA_TYPE 
FROM information_schema.columns WHERE table_name = 'TABLE1'
AND COLUMN_NAME = 'CHANGE_BY_CD') AS COL_LEN

Is there any solution to this

Thanks

Answer 1

You are running into an issue with what is called a "bridge query", which is a query that references system catalog tables (via views) and native Netezza tables. In these sorts of queries it's not uncommon to run into a data type and function compatibility issues.

Not to get too deep into those weeds, I'll cut to the chase and say that if you resort to a case statement to resolve the data type you can avoid those issues in this particular case.

For CHARACTER, VARCHAR, NCHARACTER, and NVARCHAR you can subtract 12 from the value in ATTTYPMOD to get the character length of the column as defined in the DDL.

SELECT 'TABLE1'   AS TABLE_NAME,
   'CHANGE_BY_CD' AS COL,
   (
      SELECT data_type
      FROM (
            SELECT objname,
               attname,
               atttypid,
               ATT.ATTTYPMOD,
               CASE
                  WHEN atttypid=23 THEN 'INTEGER'
                  WHEN atttypid=16 THEN 'BOOLEAN'
                  WHEN atttypid=17 THEN 'BYTEA' 
                  WHEN atttypid=18 THEN 'CHAR'
                  WHEN atttypid=19 THEN 'NAME'
                  WHEN atttypid=20 THEN 'BIGINT'
                  WHEN atttypid=21 THEN 'SNALLINT'
                  WHEN atttypid=22 THEN 'INT2VECTOR'
                  WHEN atttypid=24 THEN 'REGPROC'
                  WHEN atttypid=25 THEN 'TEXT'
                  WHEN atttypid=26 THEN 'OID'
                  WHEN atttypid=27 THEN 'TID'
                  WHEN atttypid=28 THEN 'XID'
                  WHEN atttypid=29 THEN 'CID'
                  WHEN atttypid=30 THEN 'OIDVECTOR'
                  WHEN atttypid=210 THEN 'SMGR'
                  WHEN atttypid=700 THEN 'REAL'
                  WHEN atttypid=701 THEN 'DOUBLE'
                  WHEN atttypid=702 THEN 'ABSTIME'
                  WHEN atttypid=705 THEN 'UNKNOWN'
                  WHEN atttypid=1007 THEN '_INTEGER'
                  WHEN atttypid=1033 THEN 'ACLITEM'
                  WHEN atttypid=1034 THEN '_ACLITEM'
                  WHEN atttypid=1042 THEN 'CHARACTER(' || atttypmod -12 || ')'
                  WHEN atttypid=1043 THEN 'VARCHAR(' || atttypmod -12 || ')'
                  WHEN atttypid=1082 THEN 'DATE'
                  WHEN atttypid=1083 THEN 'TIME'
                  WHEN atttypid=1184 THEN 'TIMESTAMP'
                  WHEN atttypid=1266 THEN 'TIMETZ'
                  WHEN atttypid=1700 THEN 'NUMERIC'
                  WHEN atttypid=2500 THEN 'BYTEINT'
                  WHEN atttypid=2569 THEN 'NUCL'
                  WHEN atttypid=2570 THEN 'PROT'
                  WHEN atttypid=2522 THEN 'NCHAR(' || atttypmod -12 || ')'
                  WHEN atttypid=2530 THEN 'NVARCHAR(' || atttypmod -12 || ')'
                  WHEN atttypid=278 THEN 'NTEXT'
               END DATA_TYPE
            FROM DEFINITION_SCHEMA."_V_OBJ_RELATION"
               JOIN DEFINITION_SCHEMA."_V_ATTRIBUTE" ATT
               ON ATT.ATTRELID = DEFINITION_SCHEMA."_V_OBJ_RELATION".RELOID
               LEFT JOIN DEFINITION_SCHEMA."_T_ATTRDEF"
               ON (
                     ATT.ATTRELID = DEFINITION_SCHEMA."_T_ATTRDEF".ADRELID
                  AND ATT.ATTNUM  = DEFINITION_SCHEMA."_T_ATTRDEF".ADNUM
                  )
            WHERE (
                  ATT.ATTNUM                              > 0
               AND SUBSTR("NVARCHAR"(ATT.ATTNAME), 1, 1) <> '_'::"NVARCHAR"
               AND OBJTYPE                                ='TABLE'
               )
         )
         foo
      WHERE foo.objname = 'TABLE_1'
      AND foo.ATTNAME   = 'CHANGE_BY_CD'
   )
                                         AS COL_LEN,
   MAX(LENGTH(CHANGE_BY_CD) )            AS MAX_LEN,
   CHANGE_BY_CD::CHARACTER VARYING(2000) AS VAL
FROM TABLE1
WHERE CHANGE_BY_CD IS NOT NULL
GROUP BY CHANGE_BY_CD
ORDER BY MAX_LEN DESC ,
   CHANGE_BY_CD DESC LIMIT 1 ;