SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
Support for materialized views in PostgreSQL

 
Reply to topic    SoftTree Technologies Forum Index » Tips & Snippets Repository View previous topic
View next topic
Support for materialized views in PostgreSQL
Author Message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7841

Post Support for materialized views in PostgreSQL Reply with quote
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

Thu Dec 17, 2015 10:17 am View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » Tips & Snippets Repository 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.