1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49
|
select table_schema,
table_name,
ordinal_position as position,
column_name,
data_type,
case when character_maximum_length is not null
then character_maximum_length
else numeric_precision end as max_length,
is_nullable,
column_default as default_value
from information_schema.columns
where table_schema not in ('information_schema', 'pg_catalog')
order by table_schema,
table_name,
ordinal_position;
=> meme avec un * rien
ET
SELECT
table_name
,arr[1] AS creation
,arr[2] AS modification
FROM
(
SELECT
table_name
,(SELECT ARRAY[creation,modification]
FROM pg_stat_file('./base/' || folder || '/' || filenode)) AS arr
FROM (
SELECT
table_name
,(SELECT MAX(pg_ls_dir::int)::text
FROM pg_ls_dir('./base')
WHERE pg_ls_dir <> 'pgsql_tmp'
AND pg_ls_dir::int <=
(SELECT relfilenode FROM pg_class cls
INNER JOIN pg_namespace nsp ON cls.relnamespace = nsp.oid
WHERE nsp.nspname = table_schema AND relname ILIKE table_name)) AS folder
,(SELECT relfilenode FROM pg_class cls
INNER JOIN pg_namespace nsp ON cls.relnamespace = nsp.oid
WHERE nsp.nspname = table_schema AND relname ILIKE table_name) AS filenode
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'
AND table_schema = 'public'
) CTE
) X
=> ce sont plutot des dates d'acces au tables que des modifications de champs |
Partager