PGSQL 对象创建函数生成工具

文章目录

代码
结果
代码

<!DOCTYPE html>
<html lang="zh">
<head><meta charset="UTF-8"><meta name="viewport" content="width=device-width, initial-scale=1.0"><title>PGSQL 函数生成器</title><style>body { font-family: Arial, sans-serif; margin: 20px; }label, select, input, button { display: block; margin-bottom: 10px; }/* 下拉列表宽度自适应 */select {width: auto;min-width: 150px;}/* 文本框宽度控制 */input[type="text"] {width: 100%;max-width: 400px;padding: 5px;}/* 按钮样式 */button { width: auto; padding: 8px 16px; cursor: pointer; }/* 使用 pre 显示 SQL 代码 */pre {width: 100%;min-height: 100px;white-space: pre-wrap;word-wrap: break-word;border: 1px solid #ccc;padding: 10px;overflow: auto;}</style>
</head>
<body><h2>PGSQL 函数生成器</h2><label for="operation">选择 SQL 操作：</label><select id="operation"><option value="create_table">新建表</option><option value="add_column">新增字段</option><option value="create_sequence">新增序列</option><option value="create_index">新增索引</option><option value="add_constraint">新增约束</option></select><!-- 表名区域（除新增序列外） --><div id="tableName-section"><label for="tableName">表名：</label><input type="text" id="tableName" placeholder="请输入表名"></div><div id="details-section"><label for="details" id="details-label">建表 SQL:</label><input type="text" id="details" placeholder="例如: create table test (id bigserial primary key, info text);"></div><div id="index-section" style="display:none;"><label for="indexName">索引名称：</label><input type="text" id="indexName" placeholder="请输入索引名称"><label for="indexFields">索引字段：</label><input type="text" id="indexFields" placeholder="例如: id, info"></div><div id="constraint-section" style="display:none;"><label for="constraintName">约束名称：</label><input type="text" id="constraintName" placeholder="请输入约束名称"><label for="constraintDetails">约束信息：</label><input type="text" id="constraintDetails" placeholder="例如: CHECK (age < 200 AND age >= 0)"></div><button onclick="generateSQL()">生成 SQL</button><label for="output">生成的 SQL：</label><pre id="output"></pre><script>// 下拉列表变化时，清空所有输入框及输出，并根据选项调整显示区域document.getElementById("operation").addEventListener("change", function(){// 清空所有文本框let inputs = document.querySelectorAll("input[type='text']");inputs.forEach(input => input.value = "");// 清空输出区域document.getElementById("output").innerHTML = "";let op = this.value;let detailsLabel = document.getElementById("details-label");let detailsInput = document.getElementById("details");let indexSection = document.getElementById("index-section");let constraintSection = document.getElementById("constraint-section");let tableNameSection = document.getElementById("tableName-section");if (op === "create_table") {// 对于新建表，显示表名输入区域tableNameSection.style.display = "block";detailsLabel.innerText = "建表 SQL";detailsInput.placeholder = "例如: create table test (id bigserial primary key, info text);";detailsInput.style.display = "block";indexSection.style.display = "none";constraintSection.style.display = "none";} else if (op === "add_column") {tableNameSection.style.display = "block";detailsLabel.innerText = "字段信息";detailsInput.placeholder = "例如: age int";detailsInput.style.display = "block";indexSection.style.display = "none";constraintSection.style.display = "none";} else if (op === "create_sequence") {// 对于新增序列，不需要表名输入tableNameSection.style.display = "none";detailsLabel.innerText = "序列创建 SQL";detailsInput.placeholder = "例如: CREATE SEQUENCE test_id_seq";detailsInput.style.display = "block";indexSection.style.display = "none";constraintSection.style.display = "none";} else if (op === "create_index") {tableNameSection.style.display = "block";detailsLabel.innerText = "";  // 不再显示 "索引创建 SQL"detailsInput.style.display = "none";indexSection.style.display = "block";constraintSection.style.display = "none";} else if (op === "add_constraint") {tableNameSection.style.display = "block";detailsLabel.innerText = "";  // 不再显示 "约束创建 SQL"detailsInput.style.display = "none";indexSection.style.display = "none";constraintSection.style.display = "block";}});function generateSQL() {let operation = document.getElementById("operation").value;// 对于 create_sequence，不使用表名输入let tableName = document.getElementById("tableName").value.trim();let details = document.getElementById("details").value.trim();let indexName = document.getElementById("indexName").value.trim();let indexFields = document.getElementById("indexFields").value.trim();let constraintName = document.getElementById("constraintName").value.trim();let constraintDetails = document.getElementById("constraintDetails").value.trim();let sql = "";// 对于新增序列操作，不需要表名if (operation !== "create_sequence" && !tableName) {alert("请输入表名");return;}switch (operation) {case "create_table":if (!details) {alert("请输入建表 SQL");return;}sql = `-- 新建表
CREATE OR REPLACE FUNCTION func_AA00000()
RETURNS int AS
$BODY$
DECLARE
BEGINPERFORM NULL FROM pg_tables WHERE tablename='${tableName}';IF NOT FOUND THENCREATE TABLE ${tableName}(${details});END IF;RETURN 0;
END;
$BODY$
LANGUAGE plpgsql VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;SELECT * FROM func_AA00000();
DROP FUNCTION IF EXISTS func_AA00000();`;break;case "add_column":if (!details) {alert("请输入字段信息");return;}sql = `-- 新增字段
CREATE OR REPLACE FUNCTION func_AA00000()
RETURNS int AS
$BODY$
DECLARE
BEGINPERFORM attname FROM pg_attribute WHERE attname = '${details.split(" ")[0]}'  -- 字段名\n    AND attrelid = '${tableName}'::regclass; -- 表名IF NOT FOUND THENALTER TABLE ${tableName} ADD ${details};END IF;RETURN 0;
END;
$BODY$
LANGUAGE plpgsql VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;SELECT * FROM func_AA00000();
DROP FUNCTION IF EXISTS func_AA00000();`;break;case "create_sequence":if (!details) {alert("请输入序列创建 SQL");return;}// 提取序列名称：假设格式为 "CREATE SEQUENCE seq_name"let tokens = details.split(/\s+/);let seqName = tokens[2] ? tokens[2] : "";if (!seqName) {alert("无法解析序列名称，请检查输入格式");return;}sql = `-- 新增序列
CREATE OR REPLACE FUNCTION func_AA00000()
RETURNS int AS
$BODY$
DECLARE
BEGINPERFORM NULL FROM information_schema.sequences WHERE sequence_name='${seqName}'; -- 序列名IF NOT FOUND THEN ${details};END IF;RETURN 0;
END;
$BODY$
LANGUAGE plpgsql VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;SELECT * FROM func_AA00000();
DROP FUNCTION IF EXISTS func_AA00000();`;break;case "create_index":if (!indexName || !indexFields) {alert("请输入索引名称和索引字段");return;}sql = `-- 新增索引
CREATE OR REPLACE FUNCTION func_AA00000()
RETURNS int AS
$BODY$
DECLARE
BEGINSELECT * FROM pg_indexes WHERE tablename='${tableName}' -- 表名\n    AND indexname='${indexName}';  -- 索引名IF NOT FOUND THEN CREATE INDEX ${indexName} ON ${tableName}(${indexFields});END IF;RETURN 0;
END;
$BODY$
LANGUAGE plpgsql VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;SELECT * FROM func_AA00000();
DROP FUNCTION IF EXISTS func_AA00000();`;break;case "add_constraint":if (!constraintName || !constraintDetails) {alert("请输入约束名称和约束信息");return;}sql = `-- 新增约束
CREATE OR REPLACE FUNCTION func_AA00000()
RETURNS int AS
$BODY$
DECLARE
BEGINPERFORM conname FROM pg_constraint WHERE conname = '${constraintName}' -- 约束名\n    AND conrelid = '${tableName}'::regclass; -- 表名IF NOT FOUND THEN ALTER TABLE ${tableName} ADD CONSTRAINT ${constraintName} ${constraintDetails};END IF;RETURN 0;
END;
$BODY$
LANGUAGE plpgsql VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;SELECT * FROM func_AA00000();
DROP FUNCTION IF EXISTS func_AA00000();`;break;}// 为 SQL 文本按行处理，对注释行添加蓝色前景色let htmlContent = sql.split('\n').map(line => {if (line.trim().startsWith('--')) {return `<span style="color:blue;">${line}</span>`;}return line;}).join('\n');document.getElementById("output").innerHTML = htmlContent;}</script></body>
</html>
结果

在这里插入图片描述
PGSQL 对象创建函数生成工具

文章目录

代码

结果

相关资讯

热文排行

最新新闻

推荐新闻

热搜词
