postgreSQL实现show create table(二)
-- indexs
-- CREATE UNIQUE INDEX pg_language_oid_index ON pg_language USING btree (oid); -- table pg_language
--
/** **/
--- 获取非约束索引 column
-- CREATE UNIQUE INDEX pg_language_oid_index ON pg_language USING btree (oid); -- table pg_language
tableScript:= tableScript || array_to_string(
array(
select 'CREATE UNIQUE INDEX ' || indexrelname || ' ON ' || tablename || ' USING btree '|| '(' || attname || ');' from (
SELECT
i.relname AS indexrelname , x.indkey,
( select array_to_string (
array(
select a.attname from pg_attribute a where attrelid=c.oid and a.attnum in ( select unnest(x.indkey) )
)
,',' ) )as attname
FROM pg_class c
JOIN pg_index x ON c.oid = x.indrelid
JOIN pg_class i ON i.oid = x.indexrelid
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname=tablename and i.relname not in
( select constraint_name from information_schema.key_column_usage where table_name=tablename )
)as t
) ,',' );
-- COMMENT COMMENT ON COLUMN sys_activity.id IS '主键';
tableScript:= tableScript || array_to_string(
array(
SELECT ' COMMENT ON COLUMN' || tablename || '.' || a.attname ||' IS '|| ''''|| d.description ||''''
FROM pg_class c
JOIN pg_description d ON c.oid=d.objoid
JOIN pg_attribute a ON c.oid = a.attrelid
WHERE c.relname=tablename
AND a.attnum = d.objsubid),',') ;
return tableScript;
end
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
测试：
Sql代码
select showcreatetable('public','pg_seclabel');