My expectation is to generate DDL string from existing tables. Like most of the IDE's do with one click, but via Java. I am using PostgreSQL as database.
What I want to gather is simply this;
From mySchema.myTable
| Column A | Column B |
|---|---|
| Cell 1 | Cell 2 |
| Cell 3 | Cell 4 |
Some magical code returns create table script as String
create table myTable (
columnA ....
columnB ....
)
and so on
Generating the table definition is not that straight forward and this will not work 100% of the time.
You can use this function to generate/extract the definition of a table given an oid:
CREATE OR REPLACE FUNCTION public.tabledef(oid)
RETURNS text
LANGUAGE sql
STRICT
AS $function$
/* snatched from https://github.com/filiprem/pg-tools */
WITH attrdef AS (
SELECT
n.nspname,
c.relname,
pg_catalog.array_to_string(c.reloptions || array(select 'toast.' || x from pg_catalog.unnest(tc.reloptions) x), ', ') as relopts,
c.relpersistence,
a.attnum,
a.attname,
pg_catalog.format_type(a.atttypid, a.atttypmod) as atttype,
(SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid, true) for 128) FROM pg_catalog.pg_attrdef d
WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef) as attdefault,
a.attnotnull,
(SELECT c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type t
WHERE c.oid = a.attcollation AND t.oid = a.atttypid AND a.attcollation <> t.typcollation) as attcollation,
a.attidentity,
a.attgenerated
FROM pg_catalog.pg_attribute a
JOIN pg_catalog.pg_class c ON a.attrelid = c.oid
JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid
LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)
WHERE a.attrelid = $1
AND a.attnum > 0
AND NOT a.attisdropped
ORDER BY a.attnum
),
coldef AS (
SELECT
attrdef.nspname,
attrdef.relname,
attrdef.relopts,
attrdef.relpersistence,
pg_catalog.format(
'%I %s%s%s%s%s',
attrdef.attname,
attrdef.atttype,
case when attrdef.attcollation is null then '' else pg_catalog.format(' COLLATE %I', attrdef.attcollation) end,
case when attrdef.attnotnull then ' NOT NULL' else '' end,
case when attrdef.attdefault is null then ''
else case when attrdef.attgenerated = 's' then pg_catalog.format(' GENERATED ALWAYS AS (%s) STORED', attrdef.attdefault)
when attrdef.attgenerated <> '' then ' GENERATED AS NOT_IMPLEMENTED'
else pg_catalog.format(' DEFAULT %s', attrdef.attdefault)
end
end,
case when attrdef.attidentity<>'' then pg_catalog.format(' GENERATED %s AS IDENTITY',
case attrdef.attidentity when 'd' then 'BY DEFAULT' when 'a' then 'ALWAYS' else 'NOT_IMPLEMENTED' end)
else '' end
) as col_create_sql
FROM attrdef
ORDER BY attrdef.attnum
),
tabdef AS (
SELECT
coldef.nspname,
coldef.relname,
coldef.relopts,
coldef.relpersistence,
string_agg(coldef.col_create_sql, E',\n ') as cols_create_sql
FROM coldef
GROUP BY
coldef.nspname, coldef.relname, coldef.relopts, coldef.relpersistence
)
SELECT
format(
'CREATE%s TABLE %I.%I%s%s%s;',
case tabdef.relpersistence when 't' then ' TEMP' when 'u' then ' UNLOGGED' else '' end,
tabdef.nspname,
tabdef.relname,
coalesce(
(SELECT format(E'\n PARTITION OF %I.%I %s\n', pn.nspname, pc.relname,
pg_get_expr(c.relpartbound, c.oid))
FROM pg_class c JOIN pg_inherits i ON c.oid = i.inhrelid
JOIN pg_class pc ON pc.oid = i.inhparent
JOIN pg_namespace pn ON pn.oid = pc.relnamespace
WHERE c.oid = $1),
format(E' (\n %s\n)', tabdef.cols_create_sql)
),
case when tabdef.relopts <> '' then format(' WITH (%s)', tabdef.relopts) else '' end,
coalesce(E'\nPARTITION BY '||pg_get_partkeydef($1), '')
) as table_create_sql
FROM tabdef
$function$
;
You can then call the function like this SELECT tabledef('<tablename>'::regclass::oid);
This version fetches the first oid matching the tablename. This could collide with a index or something similar if the name is not unique. In that case you need to check pg_class and find the oid that way. (SELECT * FROM pg_catalog.pg_class WHERE relname = '<tablename>').
CREATE TEMP TABLE ... ON COMMIT DROP. In practice, running pg_dump -s is usually preferable.