SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
7.4.435 missing materialized views in SQL Editor for Postgre

 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
7.4.435 missing materialized views in SQL Editor for Postgre
Author Message
omikron



Joined: 28 Oct 2015
Posts: 10
Country: Germany

Post 7.4.435 missing materialized views in SQL Editor for Postgre Reply with quote
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?
Wed Dec 16, 2015 11:41 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Reply with quote
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.
Wed Dec 16, 2015 3:09 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Reply with quote
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.
Wed Dec 16, 2015 3:16 pm View user's profile Send private message
omikron



Joined: 28 Oct 2015
Posts: 10
Country: Germany

Post Reply with quote
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
Thu Dec 17, 2015 3:50 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Reply with quote
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.
Thu Dec 17, 2015 10:15 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Reply with quote
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
Thu Dec 17, 2015 10:19 am View user's profile Send private message
omikron



Joined: 28 Oct 2015
Posts: 10
Country: Germany

Post Reply with quote
Thank you!
Thu Dec 17, 2015 10:31 am View user's profile Send private message
omikron



Joined: 28 Oct 2015
Posts: 10
Country: Germany

Post Reply with quote
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).
Fri Dec 18, 2015 11:10 am View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.