Support for materialized views in PostgreSQL

submitted by user omikron

Hi,
I have made the following changes and it seems to work... I hope you will include it into the next version:
Columns Postgresql:

Code:

SELECT 'oid', 'oid', 'NP', 0 as ordinal_position
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = :SCHEMA_NAME
AND c.relname = :OBJECT_NAME
AND c.relhasoids

UNION ALL

SELECT
column_name,
REPLACE(CASE WHEN character_maximum_length is not null THEN data_type || '(' || character_maximum_length || ')'
WHEN data_type = 'numeric' AND numeric_precision is not null THEN data_type || '(' || numeric_precision || ',' || numeric_scale || ')'
ELSE
CASE WHEN data_type = 'USER-DEFINED' AND udt_name IS NOT NULL
THEN udt_schema || '.' || udt_name
ELSE data_type
END
END, '"', ''),
CASE WHEN column_default LIKE 'nextval(%' THEN 'A' ELSE '' END
|| COALESCE(substr(is_nullable,1,1), 'Y'),
ordinal_position
FROM
information_schema.columns
WHERE
table_schema = :SCHEMA_NAME
AND table_name = :OBJECT_NAME

-- BEGIN AD
union all

SELECT attname AS collumn_name,
format_type (atttypid, atttypmod),
'Y',
attnum AS ordinal_position
FROM pg_attribute
WHERE attrelid = (:SCHEMA_NAME || '.' || :OBJECT_NAME)::regclass
AND attnum > 0
AND NOT attisdropped
-- END AD

ORDER BY
ordinal_position

Columns Postgresql Keys
SELECT 'oid', 'oid', 'NP', 0 as ordinal_position
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = :SCHEMA_NAME
AND c.relname = :OBJECT_NAME
AND c.relhasoids

UNION ALL

SELECT
column_name,
REPLACE(CASE WHEN character_maximum_length is not null THEN data_type || '(' || character_maximum_length || ')'
WHEN data_type = 'numeric' AND numeric_precision is not null THEN data_type || '(' || numeric_precision || ',' || numeric_scale || ')'
ELSE
CASE WHEN data_type = 'USER-DEFINED' AND udt_name IS NOT NULL
THEN udt_schema || '.' || udt_name
ELSE data_type
END
END, '"', ''),
CASE WHEN column_default LIKE 'nextval(%' THEN 'A' ELSE '' END
|| COALESCE(substr(is_nullable,1,1), 'Y') /* N,Y */
|| COALESCE(( /* P,F,U */
select max(substr(tc.constraint_type,1,1))
from information_schema.key_column_usage k, information_schema.table_constraints tc
where k.constraint_schema = tc.constraint_schema and k.constraint_name = tc.constraint_name
and k.table_schema = c.table_schema and k.table_name = c.table_name and k.column_name = c.column_name
),'')
|| COALESCE(( /* I */
select max('I')
from pg_catalog.pg_indexes i
where i.schemaname = c.table_schema
and i.tablename = c.table_name
and replace(replace(replace(i.indexdef, '(', ','), ')', ','), ' ', '') LIKE ('%,' || c.column_name || ',%')
),' '),
ordinal_position
FROM
information_schema.columns c
WHERE
table_schema = :SCHEMA_NAME
AND table_name = :OBJECT_NAME

-- BEGIN AD
union all

SELECT attname AS collumn_name,
format_type (atttypid, atttypmod),
'Y',
attnum AS ordinal_position
FROM pg_attribute
WHERE attrelid = (:SCHEMA_NAME || '.' || :OBJECT_NAME)::regclass
AND attnum > 0
AND NOT attisdropped
-- END AD

ORDER BY
ordinal_position

Objects Postgresql min version 8.2

Code:

select
table_name,
case table_type
when 'BASE TABLE' then 'TA'
when 'LOCAL TEMPORARY' then 'TA'
when 'VIEW' then 'VI'
else table_type
end,
cast(NULL as TIMESTAMP) as create_time,
cast(NULL as TIMESTAMP) as alter_time
from
information_schema.tables
where
table_schema = :SCHEMA_NAME
and table_type is not null

union all

-- BEGIN AD
SELECT oid::regclass::TEXT,
'VI',
CAST (NULL AS TIMESTAMP) AS create_time,
CAST (NULL AS TIMESTAMP) AS alter_time
FROM pg_class
WHERE relkind = 'm'

union all
-- END AD

select
routine_name,
CASE data_type
WHEN 'trigger' THEN 'PC'
WHEN 'void' THEN 'PR'
ELSE routine_type
END,
min(created),
max(last_altered)
from
information_schema.routines
where
routine_schema = :SCHEMA_NAME
group by
routine_name, routine_type, data_type

union all

SELECT c.relname as sequence_name, 'SQ',
cast(NULL as TIMESTAMP) as create_time,
cast(NULL as TIMESTAMP) as alter_time
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_namespace n
ON n.oid=c.relnamespace
AND n.nspname = :SCHEMA_NAME
WHERE c.relkind = 'S'

union all

SELECT trigger_name, 'TR', max(created), max(created)
FROM information_schema.triggers
WHERE trigger_schema = :SCHEMA_NAME
GROUP BY trigger_name