![SoftTree Technologies](templates/GreenTech/images/logo.gif) |
SoftTree Technologies
Technical Support Forums
|
|
Author |
Message |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
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:
![](templates/GreenTech/images/code_header.gif) |
![](templates/GreenTech/images/spacer.gif) |
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
![](templates/GreenTech/images/code_header.gif) |
![](templates/GreenTech/images/spacer.gif) |
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 |
|
![](templates/GreenTech/images/spacer.gif) |
|
|
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
|
|
|