Yes! This was a pain, but I did manage to solve this myself. I am posting an answer for others who may face this problem.
In summary, I made a function that creates a materialized view with forced lower case column names. I also added an option to include an incrementing integer gid that ArcMap requires, and in some PostGIS spatial tables may be missing. Note there is no error checking here, so a table with columns "AAA" and "aaa" which would work fine if these identifiers were quoted will now fail. Also, ArcMap requires that all identifiers coming from PostgreSQL must be 31 characters or less. A sanitize function should ideally be able to handle this as well.
The following function:
Extracts the table definition SQL for the table of interest and lower cases the column names. (I did this by modifying the function given by Toader at https://stackoverflow.com/questions/2593803/how-to-generate-the-create-table-sql-statement-for-an-existing-table-in-postgr
Creates a new materialized view with the same table name in lower case, with a suffix appended ('_arcmap') by default, which can be set using the view_suffix parameter. It drops this materialized view first if it exists.
Optionally creates a new index column for ArcMap called gid (create_gid option). This is helpful if the spatial table does not already have a numerical integer index.
The function below can be called as follows:
SELECT create_sanitized_view_arcmap('mySpatialTable', true, 'arcmap')
and will create a new materialized view called myspatialtable_arcmap with an integer row index called gid. For error checking purposes it will return the SQL used to created the materialized view.
CREATE OR REPLACE FUNCTION create_sanitized_view_arcmap
(p_table_name
VARCHAR,
create_gid
BOOLEAN DEFAULT TRUE,
view_suffix
VARCHAR DEFAULT 'arcmap')
RETURNS TEXT
AS
$BODY$
DECLARE
v_table_ddl TEXT;
column_record RECORD;
BEGIN
FOR column_record IN
SELECT
b.nspname AS schema_name,
b.relname AS table_name,
a.attname AS column_name,
pg_catalog.format_type(a.atttypid, a.atttypmod) AS column_type,
CASE WHEN
(SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) FOR 128)
FROM pg_catalog.pg_attrdef d
WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef) IS
NOT NULL
THEN
'DEFAULT ' ||
(SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) FOR 128)
FROM pg_catalog.pg_attrdef d
WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef)
ELSE
''
END AS column_default_value,
CASE WHEN a.attnotnull = TRUE
THEN
'NOT NULL'
ELSE
'NULL'
END AS column_not_null,
a.attnum AS attnum,
e.max_attnum AS max_attnum
FROM
pg_catalog.pg_attribute a
INNER JOIN
(SELECT
c.oid,
n.nspname,
c.relname
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname ~ ('^(' || p_table_name || ')$')
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 2, 3) b
ON a.attrelid = b.oid
INNER JOIN
(SELECT
a.attrelid,
max(a.attnum) AS max_attnum
FROM pg_catalog.pg_attribute a
WHERE a.attnum > 0
AND NOT a.attisdropped
GROUP BY a.attrelid) e
ON a.attrelid = e.attrelid
WHERE a.attnum > 0
AND NOT a.attisdropped
ORDER BY a.attnum
LOOP
IF column_record.attnum = 1
THEN
v_table_ddl:='CREATE MATERIALIZED VIEW ' || LOWER(column_record
.table_name)
|| '_' || view_suffix || ' AS (SELECT ';
IF create_gid = TRUE
THEN
v_table_ddl:=v_table_ddl || chr(10) || '(ROW_NUMBER() OVER ())
::integer
AS gid,';
END IF;
ELSE
v_table_ddl:=v_table_ddl || ',';
END IF;
IF column_record.attnum <= column_record.max_attnum
THEN
v_table_ddl:=v_table_ddl || chr(10) ||
' "' || column_record.column_name || '" AS ' ||
LOWER(column_record.column_name);
END IF;
END LOOP;
v_table_ddl:=v_table_ddl || ' FROM "' || column_record.schema_name ||
'"."' || column_record.table_name || '");';
EXECUTE format('DROP MATERIALIZED VIEW IF EXISTS %s',
LOWER(column_record.table_name) || '_' || view_suffix);
EXECUTE format('%s', v_table_ddl);
RETURN v_table_ddl;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;
