1. Базовый подход с конкатенацией строк
Предположим, у нас есть таблица employees:
CREATE TABLE employees (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
department VARCHAR(50),
salary NUMERIC(10,2) );
Простой HTML-отчет:
SELECT
'<!DOCTYPE html>
<html>
<head>
<title>Employee Report</title>
<style>
table { border-collapse: collapse; width: 100%; }
th, td { border: 1px solid #ddd; padding: 8px; text-align: left; }
th { background-color: #f2f2f2; }
tr:nth-child(even) { background-color: #f9f9f9; }
</style>
</head>
<body>
<h1>Employee Report</h1>
<table>
<tr>
<th>ID</th>
<th>Name</th>
<th>Department</th>
<th>Salary</th>
</tr>'
||
string_agg(
format(
'<tr><td>%s</td><td>%s</td><td>%s</td><td>%s</td></tr>',
id,
name,
department,
salary
),
''
)
||
'</table>
<p>Generated on: ' || CURRENT_TIMESTAMP || '</p>
</body>
</html>' as html_report
FROM employees;
2. Улучшенная версия с функциями PostgreSQL
-- Функция для генерации HTML-отчета
CREATE OR REPLACE FUNCTION generate_employee_report()
RETURNS TEXT AS $$
DECLARE
html_content TEXT;
total_employees INT;
avg_salary NUMERIC;
BEGIN
-- Получаем общее количество сотрудников и среднюю зарплату
SELECT COUNT(*), AVG(salary) INTO total_employees, avg_salary
FROM employees;
-- Генерируем HTML
html_content :=
'<!DOCTYPE html>
<html>
<head>
<title>Employee Report</title>
<style>
body { font-family: Arial, sans-serif; margin: 20px; }
table { border-collapse: collapse; width: 100%; margin: 20px 0; }
th, td { border: 1px solid #ddd; padding: 12px; text-align: left; }
th { background-color: #4CAF50; color: white; }
tr:hover { background-color: #f5f5f5; }
.summary { background-color: #e7f3ff; padding: 15px; border-radius: 5px; }
</style>
</head>
<body>
<h1>Employee Report</h1>
<div class="summary">
<strong>Summary:</strong> ' || total_employees ||
' employees, Average Salary: $' || ROUND(avg_salary, 2) || '
</div>
<table>
<tr>
<th>ID</th>
<th>Name</th>
<th>Department</th>
<th>Salary</th>
</tr>';
-- Добавляем строки с данными
html_content := html_content || (
SELECT string_agg(
format(
'<tr><td>%s</td><td>%s</td><td>%s</td><td>$%s</td></tr>',
id,
name,
department,
salary
),
''
)
FROM employees
ORDER BY department, name
);
html_content := html_content ||
'</table>
<p><em>Report generated on: ' || TO_CHAR(CURRENT_TIMESTAMP, 'YYYY-MM-DD HH24:MI') || '</em></p>
</body>
</html>';
RETURN html_content;
END;
$$ LANGUAGE plpgsql;
-- Вызов функции
SELECT generate_employee_report();
3. Отчет с группировкой по отделам
SELECT
'<!DOCTYPE html>
<html>
<head>
<title>Department Report</title>
<style>
.department { background-color: #e7f3ff; margin: 10px 0; padding: 10px; }
table { width: 100%; border-collapse: collapse; }
th, td { border: 1px solid #ddd; padding: 8px; }
</style>
</head>
<body>
<h1>Department Report</h1>'
||
(
SELECT string_agg(
format(
'<div class="department">
<h2>%s Department</h2>
<table>
<tr><th>Name</th><th>Salary</th></tr>
%s
</table>
<p><strong>Total: $%s</strong></p>
</div>',
dept_data.department,
dept_data.employee_rows,
dept_data.total_salary
),
''
)
FROM (
SELECT
department,
string_agg(
format('<tr><td>%s</td><td>$%s</td></tr>', name, salary),
''
) as employee_rows,
SUM(salary) as total_salary
FROM employees
GROUP BY department
ORDER BY department
) as dept_data
)
||
'</body>
</html>' as html_report;
4. Сохранение результата в файл
Чтобы сохранить HTML в файл на сервере, можно использовать несколько подходов:
Через psql (командная строка):
psql -d your_database -c "SELECT generate_employee_report();" -t -A > report.html
Через функцию с записью в файл (требует права суперпользователя):
-- Создаем функцию для записи в файл
CREATE OR REPLACE FUNCTION save_html_report(file_path TEXT)
RETURNS VOID AS $$
DECLARE
html_content TEXT;
BEGIN
SELECT generate_employee_report() INTO html_content;
-- Записываем в файл (требует прав суперпользователя)
EXECUTE format('COPY (SELECT %L) TO %L', html_content, file_path);
END;
$$ LANGUAGE plpgsql;
-- Вызываем функцию
SELECT save_html_report('/tmp/employee_report.html');
5. Практический пример с обработкой NULL значений
CREATE OR REPLACE FUNCTION generate_safe_html_report()
RETURNS TEXT AS $$
BEGIN
RETURN '
<!DOCTYPE html>
<html>
<head>
<title>Safe Employee Report</title>
<style>
table { border-collapse: collapse; width: 100%; }
th, td { border: 1px solid #ddd; padding: 8px; }
.null-value { color: #999; font-style: italic; }
</style>
</head>
<body>
<h1>Employee Report</h1>
<table>
<tr>
<th>ID</th>
<th>Name</th>
<th>Department</th>
<th>Salary</th>
</tr>'
||
COALESCE((
SELECT string_agg(
format(
'<tr><td>%s</td><td>%s</td><td>%s</td><td>%s</td></tr>',
id,
COALESCE(name, '<span class="null-value">Not specified</span>'),
COALESCE(department, '<span class="null-value">Not assigned</span>'),
COALESCE('$' || salary::text, '<span class="null-value">Not set</span>')
),
''
)
FROM employees
ORDER BY id
), '<tr><td colspan="4">No data available</td></tr>')
||
'</table>
</body>
</html>';
END;
$$ LANGUAGE plpgsql;
Советы по использованию:
- Экранирование символов: Для реальных данных добавьте экранирование HTML-символов
- Производительность: Для больших таблиц используйте пагинацию
- Форматирование: Добавляйте CSS-стили для лучшего внешнего вида
- Безопасность: Проверяйте входные данные при использовании пользовательского ввода
Этот подход позволяет генерировать готовые HTML-отчеты непосредственно в PostgreSQL без дополнительных инструментов.