Макросы в pgAdmin. Генерация шаблонов select,insert,update,delete,truncate для конкретной таблицы

Во время написания кода sql- или plpgsql-функций  хочется уменьшить время, которое затрачивается на реализацию типовых фрагментов. К таким фрагментам относятся операторы select, insert,update,delete,truncate  при работе с  таблицами. К сожалению в pgadmin отсутствует функция автозавершения набираемого текста и поэтому нет возможности быстро генерировать команды манипуляции с таблицами с выбором шаблонов этих  команд из контекстного меню.

Чтобы восполнить  этот пробел я написал pgadmin-макрос, генерирующий шаблоны команд select, insert,update,delete,truncate для таблицы с подстановкой соответствующих полей этой таблицы.

Текст макроса:

SELECT table_schema,
       TABLE_NAME,
       'select ' || string_agg(COLUMN_NAME, ',') || ' from ' || table_schema || '.' || TABLE_NAME AS query_select,
                                                                                                     'insert into ' || table_schema || '.' || TABLE_NAME || '(' || string_agg(COLUMN_NAME, ',') || ') values()' AS query_insert,
                                                                                                                                                            'update ' || table_schema || '.' || TABLE_NAME || ' set ' || string_agg(COLUMN_NAME, '= ,') || ' where' AS query_update,
'delete from '|| table_schema || '.' || TABLE_NAME || ' where ' AS query_delete,
                                                      'truncate table ' || table_schema || '.' || TABLE_NAME || ' restart identity cascade' AS query_truncate
FROM information_schema.columns
WHERE NOT (table_schema ILIKE 'pg_%'
           OR table_schema ILIKE 'information_%')
AND TABLE_NAME ILIKE ('%' || trim('$SELECTION$') || '%')
GROUP BY TABLE_NAME,
         table_schema;

Пример:

1. Создадим таблицу table1

CREATE TABLE table1( id serial PRIMARY KEY, name varchar(255), code varchar(20), datetime TIMESTAMP DEFAULT now());

2. Выделим  название "table1" и выполним наш макрос

3. Результатом выполнения макроса в pgadmin будет следующая таблица

Тексты полученных шаблонов команд для таблицы будут следующие:

select id,name,code,datetime from public.table1

insert into public.table1(id,name,code,datetime) values()

update public.table1 set id= ,name= ,code= ,datetime where

delete from public.table1 where

truncate table public.table1 restart identity cascade

4. Далее можно скопировать текст нужного шаблона и использовать в написании sql-кода. 

Благодаря макросу мы фактически  получили тексты 5 основных шаблонных команд для работы с таблицей - select,insert,update,delete,truncate.  Если таблица большая и содержит  множество полей - это тем более полезно, не нужно выполнять дополнительные действия по уточнению наименований полей. Таким образом, этот макрос позволяет сократить время работы и увеличить производительность программиста. 

Посмотреть работу макроса можно в следующем на примере по ссылке 

Макросы в pgAdmin. Количество записей в таблице и размер в мегабайтах.

Я уже рассказывал про макросы для замечательной программы pgAdmin в предыдущем посте. В этом сообщении хочу поделиться еще двумя полезными макросами, которые могут пригодиться для оценки объема таблиц и не только.

1. Макрос для получения количества строк в таблице, чье название выделено в окне Query Tool.
   
   

   SELECT COUNT(*) FROM $SELECTION$

   
   
2. Макрос, позволяющий определить размер объекта, в название которого входит выделенный в Query Tool текст. Среди таких объектов могут быть таблицы, индексы, последовательности (sequence).
   
   

   SELECT pg_class.relkind AS "Тип", pg_namespace.nspname AS "Схема", pg_class.relname AS "Имя", 
   pg_class.relpages::FLOAT*8192/1024/1024 AS "Размер (Мб)", 
   pg_class.reltuples AS "Записей" FROM pg_class
   LEFT JOIN pg_namespace ON pg_class.relnamespace=pg_namespace.oid 
   WHERE 
   NOT pg_class.relname LIKE 'pg_%' 
   AND NOT (pg_namespace.nspname ILIKE 'pg_%' OR pg_namespace.nspname ILIKE 'information_%') 
   AND pg_class.relname ILIKE ('%' || TRIM('$SELECTION$') ||  '%')  ORDER BY pg_class.relpages DESC;

Благодаря этим макросам можно оперативно оценить объем тех или иных объектов в базе.

Макросы в pgAdmin. Список таблиц и поля таблицы.

Я работаю программистом и мне часто приходится иметь дело с базами данных. Основная часть моих обязанностей на текущей работе -это разработка схемы БД для Postgresql и создание функций на языке pgSQL. Для разработки кода функций используется программа pgAdmin 3, поставляемая вместе с PostgreSQL. Программа бесплатна, но не всегда удобна. Особенно это касается задач, связанных с созданием структуры СУБД. Для этих целей лучше всего подходит PostgreSQL Maestro, чей дружелюбный интерфейс позволяет упростить и ускорить построение структуры базы.

Однако за долгое время работы с этими программами я заметил, что pgAdmin гораздо стабильней работает, чем PostgreSQL Maestro. Последняя в случае недоступности сервера БД может просто  завершиться с ошибкой, не давая возможности сохранить набранный текст. Другой проблемой этой программы является отображение не в той кодировке сообщений от сервера БД. Из-за этого часто невозможно узнать, в чем причина ошибки того или иного запроса. Поэтому для написания и отладки функций я использую родную программу pgAdmin.

Однако и при написании кода возникает потребность узнать точное наименование таблицы ее структуру. Раньше приходилось постоянно лазить дереву с таблицами и искать нужные названия полей. Это замедляло написание коды. Однако недавно я открыл для себя

замечательную функцию этой программы - макросы.

Макрос - это команда SQL, которая может запускаться по нажатию определенного сочетания клавиш в окне Query Tool.Макросы предназначены для выполнения каких-либо типовых операций.

Чтобы создать макрос, необходимо нажать на команду "Макрос" в главном меню Query Tool. 

В появившемся окне слева выбираем сочетание клавиш, а справа вставляем текст макроса, который будет соответствовать данному сочетанию клавиш. 

Макросу нужно задать имя, а затем нажать кнопку "Сохранить".

 Текст макроса представляет собой обычный запрос, за исключением специального ключевого слова $SELECTION$. В это слово подставляется выделенный при редактировании в Query Tool текст. 

Получается, чтобы узнать какую-либо информацию о таблице, вовсе не обязательно писать полный запрос к этой таблице, достаточно написать ее имя, выделить данное имя и запустить макрос, запрашивающий нужную информацию.

А теперь приведу примеры полезных макросов, которые я использую в своей работе.

1.  Макрос, получающий список таблиц для схемы public базы данных PostgreSQL.
   
   
   SELECT * FROM information_schema.TABLES

   WHERE 
   table_type='BASE TABLE' AND table_schema='public'
   ORDER BY table_name




2.  Макрос, получающий список полей в таблице, чье название выделено (очень не хватало этой возможности, когда я перешел с MS SQL Server 2008, в Microsoft Management Studio она была по-умолчанию).
   
   

   SELECT quote_ident(nspname) || '.' || quote_ident(relname) AS table_name, 
          quote_ident(attname) AS field_name, 
          format_type(atttypid,atttypmod) AS field_type, 
          case when attnotnull then ' NOT NULL' else '' end AS null_constraint,
          case when atthasdef then 'DEFAULT ' || 
                                   ( SELECT pg_get_expr(adbin, attrelid) 
                                       FROM pg_attrdef 
                                      WHERE adrelid = attrelid AND adnum = attnum )::text else ''
          end AS dafault_value,
          case when nullif(confrelid, 0) IS NOT NULL
               then confrelid::regclass::text || '( ' || 
                    array_to_string( ARRAY( SELECT quote_ident( fa.attname ) 
                                              FROM pg_attribute AS fa 
                                             WHERE fa.attnum = ANY ( confkey ) 
                                               AND fa.attrelid = confrelid
                                             ORDER BY fa.attnum 
                                           ), ','
                                    ) || ' )'
               else '' end AS references_to
     FROM pg_attribute 
          LEFT OUTER JOIN pg_constraint ON conrelid = attrelid 
                                       AND attnum = conkey[1] 
                                       AND array_upper( conkey, 1 ) = 1,
          pg_class, 
          pg_namespace
    WHERE pg_class.oid = attrelid
      AND pg_namespace.oid = relnamespace
      AND relname = btrim( '$SELECTION$' )
      AND attnum > 0
      AND NOT attisdropped
    ORDER BY attrelid, attnum;

Таким образом, когда, в случае разработки, я забываю точное название таблицы, я выполняю команду для первого макроса. И у меня перед глазами появляется весь список таблиц схемы public. А когда мне хочется узнать список полей данной таблицы, то я просто выделяю ее название и запускаю второй макрос. Теперь разработка PgSql-функций для меня значительно ускорилась :)

Активация расширений Google Chrome по нажатию клавиш

Все больше открываю для себя замечательных возможностей браузера Google Chrome. Мне как программисту, освоившему метод слепой печати, не всегда нравится серфить в Интернете, используя только мышь. При этом, я часто сохраняю полезные и интересные заметки из Интернета с помощью сервиса www.evernote.com. В моем браузере установлен плагин Evernote Web Clipper, который позволяет выделенный на веб-странице текст или всю веб страницу отправить в мой блокнот для последующего ознакомления. Для запуска этого плагина я всегда использовал контекстное меню и не знал, что каждому установленном плагину в Chrome для запуска можно назначить функциональную клавишу. Это делается следующим образом:

1. Кликаем на пункт "Настройки" главного меню браузера
2. Слева будет один из трех пунктов - "Расширения". Кликаем по нему.
3. Пролистав список установленных расширений внизу справа Вы увидите пункт "Настроить команды"
4. Появится вот такое окошечко, где можно задать комбинации клавиш для активизации плагина (в данном случае комбинация Ctrl+E запускает плагин Evernote Web Clipper)

Этот способ позволил мне ускорить сохранение заметок в Evernote, и сделал еще более продуктивным процесс получения знаний посредством Интернет-серфинга.