http://wiki.isikhnas.com/index.php?title=Database_tables&feed=atom&action=historyDatabase tables - Revision history2024-03-28T20:54:35ZRevision history for this page on the wikiMediaWiki 1.31.16http://wiki.isikhnas.com/index.php?title=Database_tables&diff=825&oldid=prevCatriona: Created page with "{{#apGetSQL:select rec from ( select tablename, schema, format ('<h3>%s</h3> <P>%s</p> <dt>Schema</dt><dd>%s</dd> CREATE TABLE %s ( %s );',tablename, description, schema, ta..."2014-02-25T03:30:55Z<p>Created page with "{{#apGetSQL:select rec from ( select tablename, schema, format ('<h3>%s</h3> <P>%s</p> <dt>Schema</dt><dd>%s</dd> CREATE TABLE %s ( %s );',tablename, description, schema, ta..."</p>
<p><b>Halaman baru</b></p><div>{{#apGetSQL:select rec from (<br />
select tablename, schema, format ('<h3>%s</h3><br />
<P>%s</p><br />
<dt>Schema</dt><dd>%s</dd><br />
CREATE TABLE %s (<br />
%s<br />
);',tablename, description, schema, tablename, string_agg(fdef,',<br />
')) as rec <br />
from <br />
(SELECT <br />
descr.description as description,<br />
n.nspname as schema,<br />
c.relname as tablename,<br />
format(' %s %s%s%s%s%s%s',<br />
f.attname, <br />
pg_catalog.format_type(f.atttypid,f.atttypmod),<br />
CASE WHEN p.contype = 'p' THEN ' PRIMARY KEY' ELSE '' END,<br />
CASE WHEN p.contype = 'u' THEN ' UNIQUE' ELSE '' END,<br />
case when f.attnotnull then ' NOT NULL' ELSE '' END,<br />
CASE WHEN f.atthasdef = 't' THEN format(' DEFAULT %s',d.adsrc) ELSE '' END,<br />
CASE WHEN p.contype = 'f' THEN format(' REFERENCES %s', g.relname) END<br />
) as fdef<br />
FROM pg_attribute f <br />
JOIN pg_class c ON c.oid = f.attrelid <br />
JOIN pg_type t ON t.oid = f.atttypid <br />
LEFT JOIN pg_attrdef d ON d.adrelid = c.oid AND d.adnum = f.attnum <br />
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace <br />
LEFT JOIN pg_constraint p ON p.conrelid = c.oid AND f.attnum = ANY (p.conkey) <br />
LEFT JOIN pg_class AS g ON p.confrelid = g.oid <br />
LEFT OUTER JOIN pg_description descr on c.oid = descr.objoid <br />
WHERE c.relkind = 'r'::char <br />
AND not n.nspname in ('public', 'pg_catalog')<br />
AND f.attnum > 0 <br />
ORDER BY c.relname, f.attnum) as foo<br />
group by schema, tablename, description) as bar<br />
order by tablename<br />
;|}}</div>Catriona