Slicna pitanja si nedavno postavljao za Firebird:)
Generalno, sve ti se nalazi u semama pg_catalog i/ili information_schema.
tabele
Code:
SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'public' -- konkretna sema
AND table_type = 'BASE TABLE' -- (BASE TABLE, LOCAL TEMPORARY, VIEW)
ORDER BY table_name
kolone
Code:
SELECT table_name, column_name, data_type
FROM information_schema.columns
WHERE table_schema = 'public'
ORDER BY table_name, ordinal_position
primarni kljucevi
Code:
SELECT c.relname AS table_name,
a.attname AS column_name,
CASE WHEN t.typtype = 'd'
THEN pg_catalog.format_type(t.typbasetype, NULL)
ELSE pg_catalog.format_type(a.atttypid, NULL)
END AS data_type
FROM pg_class AS c
JOIN pg_constraint AS o
ON c.oid = o.conrelid
AND o.contype = 'p'
JOIN pg_attribute AS a
ON a.attrelid = c.oid
AND a.attnum = ANY (o.conkey)
JOIN pg_namespace AS n
ON n.nspname = 'public'
AND c.relnamespace = n.oid
JOIN pg_type AS t
ON a.atttypid = t.oid
ORDER BY c.relname, a.attnum
Ovo sam ja koristio u nekom svom programu. Foreign key mi nisu trebali pa ih zato ni ne saljem, a mrzi me da kopam po semama :)
"The best code is no code at all."
- Zidar (ES član)
"Biggest obstacle to learning
SQL is unlearning procedural
programming." - Joe
Celko
"Minimize code, maximize data."
- A. Neil Pappalardo