Node TABLE for PostgreSQL
[EN]
List of tables in the selected PostgreSQL database schema. The list can be filtered by entering part of the table name in the "Like name object" field
SELECT c.oid,
'core' as "Schema",
c.relname as "Name",
(select count(inhrelid)
from pg_inherits
where inhparent = c.oid
) "CountPar",
CASE c.relkind
WHEN 'r' THEN 'table'
WHEN 't' THEN 'TOAST'
WHEN 'f' THEN 'foreign table'
WHEN 'p' THEN 'partition_table'
END as "Type",
pg_catalog.pg_get_userbyid(c.relowner) as "Owner",
pg_catalog.obj_description(c.oid, 'pg_class') as "Description",
u.usename
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_user u on c.relowner = u.usesysid
WHERE c.relkind IN ('r','t','f','p')
AND c.relispartition = false
and c.relnamespace = (select oid
from pg_catalog.pg_namespace n
where nspname = $$SCHEMA_NAME)
ORDER BY c.relname
If several nodes are selected in the tree (using the Ctrl button), then the program generates SQL commands for all selected nodes
Menu
Copy name - copy the object name to the clipboard
Reindex - generate a command to rebuild indexes on a table
Create code [Select] - generate the text of the request for the object (the select phrase displays the names of all fields)
Create code [Insert] - generate an insert command to insert data
Create code [Delete] - generate a delete command to delete all data
Create code [Truncate] - generate the truncate command to clear the table
Create code [Drop] - generate a drop command to delete a table
Get source - generate table creation code
Get statistics - Display table usage statistics. Data is displayed on the Statistics tab of the main window
View data (50 rows) - display table data (limit 50)
Finding functions using a table - search for functions using the selected table. The search is performed in the main process and can take a long time
Finding functions using a table
select t.*, f.* from
(
SELECT table_name, table_schema,
table_schema||'.'||table_name full_name,
table_type
FROM information_schema.tables
WHERE table_schema = $$TABLESCHEMA
and table_name = $$TABLENAME
) t
JOIN
(
SELECT n.nspname AS schema_name,
p.proname AS function_name,
pg_get_function_arguments(p.oid) AS args,
pg_get_functiondef(p.oid) AS func_def
FROM pg_proc p
JOIN pg_namespace n ON n.oid = p.pronamespace
WHERE n.nspname not in ('pg_catalog','information_schema')
and n.nspname not like 'pg%'
) f
on position(t.table_name in f.func_def) >0
Child nodes
[RU]
Список таблиц в выбранной схеме базы данных PostgreSQL. Список может быть отфильтрован вводом части имени таблиц в поле "Like name object"
SELECT c.oid,
'core' as "Schema",
c.relname as "Name",
(select count(inhrelid)
from pg_inherits
where inhparent = c.oid
) "CountPar",
CASE c.relkind
WHEN 'r' THEN 'table'
WHEN 't' THEN 'TOAST'
WHEN 'f' THEN 'foreign table'
WHEN 'p' THEN 'partition_table'
END as "Type",
pg_catalog.pg_get_userbyid(c.relowner) as "Owner",
pg_catalog.obj_description(c.oid, 'pg_class') as "Description",
u.usename
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_user u on c.relowner = u.usesysid
WHERE c.relkind IN ('r','t','f','p')
AND c.relispartition = false
and c.relnamespace = (select oid
from pg_catalog.pg_namespace n
where nspname = $$SCHEMA_NAME)
ORDER BY c.relname
Если в дереве выделено несколько узлов (с помощью кнопки Ctrl), то программа формирует SQL команды для всех выделенных узлов
Меню
Copy name - скопировать имя объекта в буфер обмена
Reindex - сформировать команду для перестройки индексов на таблице
Create code [Select] - сформировать текст запроса к объекту (во фразе select выводятся имена всех полей)
Create code [Insert] - сформировать команду insert для вставки данных
Create code [Delete] - сформировать команду delete для удаления всех данных
Create code [Truncate] - сформировать команду truncate для очистки таблицы
Create code [Drop] - сформировать команду drop для удаления таблицы
Get source - сформировать код создания таблицы
Get statistics - отобразить статистику использования таблицы. Данные выводятся на вкладке Statistics основного окна
View data (50 rows) - отобразить данные таблицы (limit 50)
Finding functions using a table - выполнить поиск функций использующих выбранную таблицу. Поиск выполняется в основном процессе и может занимать длительное время
Finding functions using a table
select t.*, f.* from
(
SELECT table_name, table_schema,
table_schema||'.'||table_name full_name,
table_type
FROM information_schema.tables
WHERE table_schema = $$TABLESCHEMA
and table_name = $$TABLENAME
) t
JOIN
(
SELECT n.nspname AS schema_name,
p.proname AS function_name,
pg_get_function_arguments(p.oid) AS args,
pg_get_functiondef(p.oid) AS func_def
FROM pg_proc p
JOIN pg_namespace n ON n.oid = p.pronamespace
WHERE n.nspname not in ('pg_catalog','information_schema')
and n.nspname not like 'pg%'
) f
on position(t.table_name in f.func_def) >0