PostgreSQL中如何在WITH查询中使用EXECUTE并存储查询结果

首先，你遇到的语法错误是因为CTE（WITH子句）无法直接嵌套EXECUTE执行动态SQL——CTE定义的是静态查询结构，而EXECUTE是用于执行动态拼接的SQL语句，两者不能直接结合使用。下面给你具体的解决方法和替代方案：

一、直接解决插入需求的正确写法 #

你的核心需求是执行动态SQL并将结果插入目标表，完全不需要借助CTE，直接把动态SQL的执行结果插入即可，不同数据库的写法略有差异：

1. PostgreSQL 环境 #

PostgreSQL支持直接在INSERT后使用EXECUTE执行动态SQL：

INSERT INTO tab_sam2 (col1, col2)
EXECUTE 'select tcol1, tcol2 from tab_sample';

2. SQL Server 环境 #

SQL Server需要用sp_executesql来执行动态SQL，搭配变量实现：

DECLARE @dynamicSql NVARCHAR(MAX);
SET @dynamicSql = 'select tcol1, tcol2 from tab_sample';

INSERT INTO tab_sam2 (col1, col2)
EXEC sp_executesql @dynamicSql;

二、存储多条数据的替代方案（无需临时表） #

如果需要先存储动态SQL的结果，再用于后续多次操作，除了临时表，还有这些方法：

1. 使用表变量（SQL Server 专属） #

表变量是内存中的临时存储，不会像临时表那样在数据库中创建物理对象，适合存储多条数据：

-- 定义表变量结构
DECLARE @tempData TABLE (col1 INT, col2 VARCHAR(100));

-- 将动态SQL结果插入表变量
DECLARE @dynamicSql NVARCHAR(MAX) = 'select tcol1, tcol2 from tab_sample';
INSERT INTO @tempData
EXEC sp_executesql @dynamicSql;

-- 后续可以多次使用@tempData的数据
SELECT * FROM @tempData;
INSERT INTO another_table SELECT * FROM @tempData;

2. 使用自定义函数返回表（PostgreSQL/SQL Server 通用） #

可以创建一个返回表类型的函数，将动态SQL的结果封装进去，后续通过调用函数获取数据：
以PostgreSQL为例：

CREATE OR REPLACE FUNCTION get_sample_data()
RETURNS TABLE(col1 INT, col2 VARCHAR(100)) AS $$
BEGIN
  RETURN QUERY EXECUTE 'select tcol1, tcol2 from tab_sample';
END;
$$ LANGUAGE plpgsql;

-- 使用函数获取数据
INSERT INTO tab_sam2 SELECT * FROM get_sample_data();
-- 后续也能重复调用
SELECT * FROM get_sample_data();

3. 嵌套CTE（仅适用于静态SQL场景） #

如果你的查询不需要动态拼接（只是示例中用了动态SQL），可以直接用静态CTE存储数据供后续操作：

WITH ttable (col1, col2) AS (
  select tcol1, tcol2 from tab_sample
)
INSERT INTO tab_sam2 SELECT col1, col2 from ttable;
-- 也可以在同一个语句里多次使用CTE
WITH ttable (col1, col2) AS (
  select tcol1, tcol2 from tab_sample
)
SELECT * FROM ttable
UNION ALL
SELECT * FROM ttable;

内容的提问来源于stack exchange，提问作者Vishal D