时间戳

首页 > 解决方案 > 我可以将“多行多列的SELECT结果”作为函数参数吗？

sql - 我可以将“多行多列的SELECT结果”作为函数参数吗？

问题描述

我想写一个通用函数将SELECT/table结果转换为text/jsonb
Convert

SELECT COALESCE(jsonb_agg(tmp)::text, '[]') FROM (
  SELECT id, balance FROM student LIMIT 5) AS tmp

至

SELECT my_to_json_string((SELECT id, balance FROM student LIMIT 5));

预计：

[{"id": 21543, "balance": 80}, {"id": 21542, "balance": 100}, {"id": 21541, "balance": 5980}, {"id": 21540, "balance": 10}, {"id": 21539, "balance": 15}]`

我可以吗？
如果没有，我应该怎么做才能避免COALESCE(jsonb_agg(tmp)::text, '[]') FROM (...) as tmp经常重复？

标签： sqlpostgresql

解决方案

CREATE OR REPLACE  FUNCTION build_details( mysqlquery text )
RETURNS jsonb AS $$
  DECLARE
    c jsonb;
  BEGIN
    EXECUTE format('SELECT COALESCE(jsonb_agg(tmp)::text, ''[]'') FROM (%s) as tmp', mysqlquery) INTO c;
    RETURN c;
  END;
$$ LANGUAGE plpgsql;

将此传递 sql 作为字符串调用

select * from build_details('SELECT * FROM foo LIMIT 5');
              build_details               
------------------------------------------
 [{"i": 1, "s": "f"}, {"i": 2, "s": "s"}]
(1 row)

在有关性能的评论中回答问题。
是的，它会更慢。
要测量它，首先打开嵌套语句分析器。

load 'auto_explain'; 
set auto_explain.log_min_duration=0; 
set auto_explain.log_nested_statements=ON; 
SET auto_explain.log_analyze = true;
set client_min_messages=DEBUG;

现在让我们运行它。

explain analyze SELECT COALESCE(jsonb_agg(tmp)::text, '[]') FROM ( SELECT * FROM foo LIMIT 5) as tmp;
LOG:  duration: 0.030 ms  plan:
Query Text: explain analyze SELECT COALESCE(jsonb_agg(tmp)::text, '[]') FROM ( SELECT * FROM foo LIMIT 5) as tmp;
Aggregate  (cost=0.15..0.17 rows=1 width=32) (actual time=0.029..0.029 rows=1 loops=1)
  ->  Subquery Scan on tmp  (cost=0.00..0.14 rows=5 width=60) (actual time=0.008..0.009 rows=2 loops=1)
        ->  Limit  (cost=0.00..0.09 rows=5 width=36) (actual time=0.006..0.006 rows=2 loops=1)
              ->  Seq Scan on foo  (cost=0.00..22.70 rows=1270 width=36) (actual time=0.005..0.005 rows=2 loops=1)
                                                     QUERY PLAN                                                     
--------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=0.15..0.17 rows=1 width=32) (actual time=0.029..0.029 rows=1 loops=1)
   ->  Subquery Scan on tmp  (cost=0.00..0.14 rows=5 width=60) (actual time=0.008..0.009 rows=2 loops=1)
         ->  Limit  (cost=0.00..0.09 rows=5 width=36) (actual time=0.006..0.006 rows=2 loops=1)
               ->  Seq Scan on foo  (cost=0.00..22.70 rows=1270 width=36) (actual time=0.005..0.005 rows=2 loops=1)
 Planning time: 0.042 ms
 Execution time: 0.143 ms
(6 rows)

对比

explain analyze select * from build_details('SELECT * FROM foo  LIMIT 5');
LOG:  duration: 0.043 ms  plan:
Query Text: SELECT COALESCE(jsonb_agg(tmp)::text, '[]') FROM (SELECT * FROM foo  LIMIT 5) as tmp
Aggregate  (cost=0.15..0.17 rows=1 width=32) (actual time=0.041..0.041 rows=1 loops=1)
  ->  Subquery Scan on tmp  (cost=0.00..0.14 rows=5 width=60) (actual time=0.007..0.009 rows=2 loops=1)
        ->  Limit  (cost=0.00..0.09 rows=5 width=36) (actual time=0.006..0.007 rows=2 loops=1)
              ->  Seq Scan on foo  (cost=0.00..22.70 rows=1270 width=36) (actual time=0.005..0.006 rows=2 loops=1)
LOG:  duration: 0.297 ms  plan:
Query Text: explain analyze select * from build_details('SELECT * FROM foo  LIMIT 5');
Function Scan on build_details  (cost=0.25..0.26 rows=1 width=32) (actual time=0.296..0.296 rows=1 loops=1)
                                                 QUERY PLAN                                                  
-------------------------------------------------------------------------------------------------------------
 Function Scan on build_details  (cost=0.25..0.26 rows=1 width=32) (actual time=0.296..0.296 rows=1 loops=1)
 Planning time: 0.024 ms
 Execution time: 0.385 ms
(3 rows)

我的猜测是，如果音量足够，差异将可以忽略不计。
其中大部分来自计划。
但是您确实需要根据您的情况和需求来检查这一点。

推荐阅读