7.4.435 missing materialized views in SQL Editor for Postgre

7.4.435 missing materialized views in SQL Editor for Postgre

Hello,
I am using SQL Editor to work with an Postgresql DB, I can't choose or autocomplete my materialized view in the PostgreSQL. Is it a bug or a feature?

Hi, materialized views aren't supported yet, SQL Assistant relies heavily on the data in the information_schema. PG doesn't expose materialized views in the information_schema, nor as tables, nor as views.

To add support for mat.views, you can customize "Objects" and "Columns" queries for PG in SQL Assistant options. See the DB Options tab, DB Queries group. If you manage to add support for mat.views, please share your results, someone else may find them helpful.

Hi,
I have made the following changes and it seems to work... I hope you will include it into the next version:
Columns Postgresql:
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
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

Thank you very much. Outstanding.

I will submit it as a formal enhancement request and also add it to Tips and Snippets forum so that other people can see and use it too.

Here is a link to the code-formatted copy of your message in Tips and Snippets
http://www.softtreetech.com/support/phpBB2/viewtopic.php?p=31211#31211

Last edited by SysOp on Thu Dec 17, 2015 10:32 am; edited 1 time in total

Thank you!

Hi,
I have found an error, the columns for views and tables were being shown twice, I have them excluded now:

Code:

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 attrelid NOT IN (SELECT (table_schema || '.' || "table_name")::regclass
      FROM information_schema.tables c)
)
AND attnum > 0
AND NOT attisdropped
-- END AD

ORDER BY
ordinal_position

and

Code:

Columns Postgresql
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 attrelid NOT IN (SELECT (table_schema || '.' || "table_name")::regclass
      FROM information_schema.tables c)
)
AND attnum > 0
AND NOT attisdropped
-- END AD

ORDER BY
ordinal_position

Can you please update also another topic (Tipps and tricks).