主页 / dba / 问题 / 326486
Accepted
Leon
Asked: 2023-04-27 13:14:24 +0800 CST

如何从 Postgresql 中的函数返回中正确选择多列？

  • 772

我在 Postgresql-11 数据库中有数百个具有相同结构的表，我需要对每个表执行相同的统计。

由于真正的统计逻辑非常复杂，为了简化我在这里的问题，假设我需要计算每个的 MAX/MIN/MEAN/STDDEV。

我不想为每个单独的表一个一个地编辑单独的 SQL，因为如果是这样，我将编写数百行非常相似的 SQL，唯一不同的是表名。

所以我在一个函数中使用动态 SQL 一次对一个表进行计算，如下所示：

DROP FUNCTION IF EXISTS get_features_for;
CREATE OR REPLACE FUNCTION get_features_for(
    IN  table_name VARCHAR,
    OUT result_ RECORD ) LANGUAGE 'plpgsql'
AS $func$

DECLARE sql_string VARCHAR :=
    format('
        SELECT ''%s'' table_name,     -- just for validating
            MAX(feature) max_feature,
            MIN(feature) min_feature,
            AVG(feature) avg_feature,
            STDDEV(feature) std_feature
        FROM "%s";',
        table_name, table_name );

BEGIN
  EXECUTE sql_string INTO result_;
END
$func$;

然后在查询中用数据库中的每个表名调用它，如下所示：

SELECT tbs."table_name", get_features_for( tbs."table_name" )
FROM information_schema."tables" tbs
WHERE tbs.table_schema = 'public' AND tbs.table_type = 'BASE TABLE'
  AND tbs."table_name" LIKE 'analy%'
ORDER BY tbs."table_name";

它有效，但结果类似于以下内容：

"table_name", "get_features_for"

“analy_001”，“（analy_001，-3,2,0,2.5）”

“analy_002”，“（analy_002，-3,2，-1,2）”

“analy_003”，“（analy_003，-3,2,0,2）”

你可以看到所有的结果列都被包装成一个字符串列，让我不能方便地使用它们。

我希望它们类似于：

表名、最大特征、最小特征、平均特征、标准特征

"analy_001", "analy_001", -3, 2, 0, 2.5

"analy_002", "analy_002", -3, 2, -1, 2

"analy_003", "analy_003", -3, 2, 0, 2

我应该怎么得到它？

顺便说一句，我正在使用 pgAdmin。

谢谢！！！

postgresql

2 个回答

  1. Best Answer

    函数的返回类型是复合类型。获取各个列的最有效方法是横向连接：

    SELECT tbs.table_name, f.*
FROM information_schema.tables tbs
   CROSS JOIN LATERAL get_features_for(tbs.table_name) AS f
WHERE tbs.table_schema = 'public'
  AND tbs.table_type = 'BASE TABLE'
  AND tbs.table_name LIKE 'analy%'
ORDER BY tbs."table_name";

    避免*并拼出实际查询中的实际列名。

    • 1

  2. 您还可以执行以下操作 - 它看起来非常高效（它广泛使用快速字符串函数，没有正则表达式 - 下面的所有代码都可以在此处的小提琴上找到）：

    CREATE TABLE test
(
  analysis TEXT NOT NULL
);

    填充：

    INSERT INTO test VALUES 
('"analy_001", "(analy_001,-3,2,0,2.5)"'),
('"analy_002", "(analy_002,-3,2,-1,2)"'),
('"analy_003", "(analy_003,-3,2,0,2)"');

    然后我们运行：

    SELECT 
  SPLIT_PART(analysis, '", "(', 2)  -- do as much of the  work here as possible!
FROM test;

    结果：

    split_part
analy_001,-3,2,0,2.5)"
analy_002,-3,2,-1,2)"
analy_003,-3,2,0,2)"

    进而：

    SELECT
  REPLACE(SPLIT_PART(analysis, '", "(', 2), ')"', '')
FROM test_;

    结果：

    replace
analy_001,-3,2,0,2.5
analy_002,-3,2,-1,2
analy_003,-3,2,0,2

    最后，我们运行：

    SELECT
  SPLIT_PART(REPLACE(SPLIT_PART(analysis, '", "(', 2), ')"', ''), ',', 1) AS table_name,
  SPLIT_PART(REPLACE(SPLIT_PART(analysis, '", "(', 2), ')"', ''), ',', 2) AS min_f,
  SPLIT_PART(REPLACE(SPLIT_PART(analysis, '", "(', 2), ')"', ''), ',', 3) AS max_f,
  SPLIT_PART(REPLACE(SPLIT_PART(analysis, '", "(', 2), ')"', ''), ',', 4) AS avg_f,
  SPLIT_PART(REPLACE(SPLIT_PART(analysis, '", "(', 2), ')"', ''), ',', 5) AS std_f
FROM
  test_;

    结果：

    table_name  min_f max_f avg_f std_f
 analy_001     -3     2     0   2.5
 analy_002     -3     2    -1     2
 analy_003     -3     2     0     2

    当然，您必须从原始 SQL 中输入您的值，但它看起来非常高效：

    SET enable_seqscan = OFF;

    进而：

    EXPLAIN (ANALYZE, BUFFERS, VERBOSE, TIMING)
SELECT
  SPLIT_PART(REPLACE(SPLIT_PART(analysis, '", "(', 2), ')"', ''), ',', 1) AS table_name,
  SPLIT_PART(REPLACE(SPLIT_PART(analysis, '", "(', 2), ')"', ''), ',', 2) AS min_feature,
  SPLIT_PART(REPLACE(SPLIT_PART(analysis, '", "(', 2), ')"', ''), ',', 3) AS max_feature,
  SPLIT_PART(REPLACE(SPLIT_PART(analysis, '", "(', 2), ')"', ''), ',', 4) AS avg_feature,
  SPLIT_PART(REPLACE(SPLIT_PART(analysis, '", "(', 2), ')"', ''), ',', 5) AS std_feature
FROM
  test_;

    结果：

    QUERY PLAN
Bitmap Heap Scan on public.test_  (cost=15.29..89.89 rows=1360 width=160) (actual time=0.028..0.034 rows=3 loops=1)
  Output: split_part(replace(split_part(analysis, '", "('::text, 2), ')"'::text, ''::text), ','::text, 1), split_part(replace(split_part(analysis, '", "('::text, 2), ')"'::text, ''::text), ','::text, 2), split_part(replace(split_part(analysis, '", "('::text, 2), ')"'::text, ''::text), ','::text, 3), split_part(replace(split_part(analysis, '", "('::text, 2), ')"'::text, ''::text), ','::text, 4), split_part(replace(split_part(analysis, '", "('::text, 2), ')"'::text, ''::text), ','::text, 5)
  Heap Blocks: exact=1
  Buffers: shared hit=2
  ->  Bitmap Index Scan on t_ix  (cost=0.00..14.95 rows=1360 width=0) (actual time=0.013..0.013 rows=3 loops=1)
        Buffers: shared hit=1
Planning Time: 0.164 ms
Execution Time: 0.100 ms

    关于 SET enable_seqscan = OFF; 的简短转移。

    • 这实际上并没有禁用顺序表扫描，它只是使它们非常昂贵 - 请参阅下面的讨论。

    • 不要在生产系统上这样做，或者至少不要在全球范围内这样做。当且仅当您完全理解任何后果时，您才可以根据具体情况、逐个查询来执行此操作，但不建议这样做。今天的查询提示是明天的错误——谨慎使用。

    • 我在这里这样做的原因是强制优化器通过顺序扫描选择索引。如果没有 enable_seqscan = OFF，这里非常小的示例表将导致优化器自动选择顺序扫描。对于生产系统上的大量记录，这应该不是问题。

    从这里的文档：

    enable_seqscan (boolean)

    启用或禁用查询计划器对顺序扫描计划类型的使用。完全抑制顺序扫描是不可能的(*)，但如果有其他方法可用，则关闭此变量会阻止计划者使用顺序扫描。默认是打开的。

    (*) 强调我的

    • 0

相关问题