regex - 如何在单个 Select 语句中执行动态 SQL 语句?
问题描述
我只是想知道如何使用一个选择来评估动态 SQL 的内容;这就是例子。这只是一个例子。但我想要动态函数,并使用单选进行管理。(我知道 sqls 只用于 SELECT 而不是修改......但在这个深层次的被问者中,我正在成为一个疯狂的开发者)
SELECT 'SELECT SETVAL(' || chr(39) || c.relname || chr(39)|| ' ,
(SELECT MAX(Id)+1 FROM ' || regexp_replace(c.relname, '_[a-zA-Z]+_[a-zA-Z]+(_[a-zA-Z0-9]+)?', '', 'g') ||' ), true );'
FROM pg_class c WHERE c.relkind = 'S';
原始输出是:
SELECT SETVAL('viewitem_id_seq' , (SELECT MAX(Id)+1 FROM viewitem ), true );
SELECT SETVAL('userform_id_seq' , (SELECT MAX(Id)+1 FROM userform ), true );
这是动态语句: (SELECT MAX(Id)+1 FROM ' || regexp_replace(c.relname, ' [a-zA-Z]+ [a-zA-Z]+(_[a-zA-Z0- 9]+)?', '', 'g')
是一个作为输出生成 SQL 的字符串,如何在同一行中评估该语句?
所需的输出是:
SELECT SETVAL('viewitem_id_seq' , 25, true );
SELECT SETVAL('userform_id_seq' , 85, true );
谢谢!
解决方案
如果这些是serial
或identity
列,则最好使用它pg_get_serial_sequence()
来获取表的列与其序列之间的链接。
您实际上可以通过使用在 SQL 语句中运行动态 SQLquery_to_xml()
如果我需要将serial
(或identity
)列的序列与其实际值同步,我使用以下脚本:
with sequences as (
-- this query is only to identify all sequences that belong to a column
-- it's essentially similar to your select * from pg_class where reltype = 'S'
-- but returns the sequence name, table and column name to which the
-- sequence belongs
select *
from (
select table_schema,
table_name,
column_name,
pg_get_serial_sequence(format('%I.%I', table_schema, table_name), column_name) as col_sequence
from information_schema.columns
where table_schema not in ('pg_catalog', 'information_schema')
) t
where col_sequence is not null
), maxvals as (
select table_schema, table_name, column_name, col_sequence,
--
-- this is the "magic" that runs the SELECT MAX() query
--
(xpath('/row/max/text()',
query_to_xml(format('select max(%I) from %I.%I', column_name, table_schema, table_name), true, true, ''))
)[1]::text::bigint as max_val
from sequences
)
select table_schema,
table_name,
column_name,
col_sequence,
coalesce(max_val, 0) as max_val,
setval(col_sequence, coalesce(max_val, 1)) --<< this uses the value from the dynamic query
from maxvals;
这里的动态部分是调用query_to_xml()
首先我format()
用来正确处理标识符。它还使编写 SQL 更容易,因为不需要连接。因此,对于第一个 CTE 返回的每个表,都会执行以下操作:
query_to_xml('select max(id) from public.some_table', true, true, '')
这会返回类似:
<row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<max>42</max>
</row>
然后使用 xpath() 从 XML 值中提取该值并转换为一个数字,然后在最终 SELECT 中使用该数字以实际调用setval()
具有多个 CTE 的嵌套仅用于使每个部分更具可读性。
例如,可以使用相同的方法来查找所有表的行数
关于如何工作的一些背景query_to_xml()
推荐阅读
- angular - 我可以使用 Identity Server 4 对我的 Angular 客户端应用程序进行预身份验证,以调用我本地托管的 .Net Core WebAPI
- angular - 未捕获(承诺):错误:无法匹配任何路由。URL 段:'tabs/tab1/discover/a1'
- php - 使用循环 Wordpress 帖子获取多封电子邮件
- regex - Perl 将 2 个正则表达式合并为 1 个
- ios - 无法在 NativeScript 7 或 8 上进行 NFC 写入工作
- django - Django 下拉列表 - 在编辑期间更改为无
- flutter - 运行 $flutter run 时找不到目标文件“lib\main.dart”
- c++ - 在 C++ 中实现“临时可暂停”并发循环
- sql - SQL从另一个值为MIN值的列中获取一个值
- javascript - 在哪里输入我想在 Bootstrap JS 中运行的函数