时间戳

首页 > 解决方案 > 将查询结果保存到列数未知的临时表中

postgresql - 将查询结果保存到列数未知的临时表中

问题描述

我正在尝试在 plpgsql 中创建以下函数：

CREATE OR REPLACE FUNCTION pg_temp.select_all(query text)
RETURNS VOID AS $$
DECLARE
    schemasT RECORD;
BEGIN
    FOR schemasT IN (
        SELECT schema_name
        FROM information_schema.schemata
        WHERE schema_name IN (
            SELECT login 
            FROM cdu.nc_tenant
        )
    ) LOOP
        SET SEARCH PATH TO schemasT;
    
        EXECUTE query INTO something;
    END LOOP;
END; $$
LANGUAGE plpgsql;

这样传入函数的任何查询都会运行到所有可用的模式中并返回查询结果。使用该功能将是这样的：

pg_temp.select_all('SELECT count(1) FROM sku')

将返回：

架构 数数
架构1 58
模式2 42

另一个例子是：

pg_temp.select_all('SELECT * FROM cdu.nc_tenant_variables where variable = ''theme''')

将返回：

架构 多变的 价值
架构1 主题 黑暗的
模式2 主题 光

为什么？

有时我需要跨模式查询事物，我正在寻找一种更好的方法来做到这一点，而不是这样做：

SELECT 'schema1' AS schema, count(1) FROM schema1.sku
UNION ALL
SELECT 'schema2' AS schema, count(1) FROM schema2.sku
UNION ALL
SELECT 'schema3' AS schema, count(1) FROM schema3.sku;

标签： postgresqlplpgsql

解决方案

我以某种方式使它工作，但它返回一个 JSON 对象而不是普通表：

DROP FUNCTION pg_temp.select_all;
CREATE OR REPLACE FUNCTION pg_temp.select_all(query TEXT, whole int DEFAULT 0)
RETURNS table (j json) AS $$
DECLARE
    schemasT RECORD;
    counter int := 1;
    test RECORD;
BEGIN
    DROP TABLE IF EXISTS resultquery;

    FOR schemasT IN (
        SELECT schema_name
        FROM information_schema.schemata
        WHERE schema_name IN (
            SELECT login 
            FROM cdu.nc_tenant
        )
    ) LOOP
        EXECUTE format('SET SEARCH_PATH TO %I', schemasT.schema_name);
        
        IF counter = 1 THEN
            EXECUTE 'CREATE TEMP TABLE resultquery AS '|| query;
            ALTER TABLE pg_temp.resultquery ADD COLUMN tenant varchar;
            TRUNCATE TABLE resultquery;
        END IF;
    
        DROP TABLE IF EXISTS temptemp;
        EXECUTE 'CREATE TEMP TABLE temptemp AS '|| query;
        ALTER TABLE pg_temp.temptemp ADD COLUMN tenant varchar;
        
        UPDATE pg_temp.temptemp SET tenant = schemasT.schema_name;
        EXECUTE 'INSERT INTO pg_temp.resultquery SELECT * FROM pg_temp.temptemp';
    
        counter := counter + 1;
    END LOOP;
    
    IF whole = 0 THEN
        RETURN QUERY  SELECT to_json(a) FROM (SELECT * FROM pg_temp.resultquery) a;
    ELSE 
        RETURN QUERY  SELECT json_agg(a) FROM (SELECT * FROM pg_temp.resultquery) a;
    END IF;
END; $$
LANGUAGE plpgsql;

使用功能：

SELECT pg_temp.select_all('select count(1) from sku', 1);

返回一个单行 JSON 对象

推荐阅读