gpt4 book ai didi

sql - 使用大量 if-else 语句和 JSONification 简化 PL/pgSQL 函数

转载 作者:行者123 更新时间:2023-11-29 11:59:47 25 4
gpt4 key购买 nike

我有 PL/PgSQL 函数,它执行非常相似的查询(唯一不同的是列名)并将输出转换为 JSON 对象。

CREATE OR REPLACE FUNCTION get_observations(kind varchar, site_id integer, var varchar) RETURNS TABLE (fc json) AS
$func$
BEGIN
IF kind = 'raw' THEN
IF var = 'o2_abs' THEN
RETURN QUERY SELECT array_to_json(array_agg(row_to_json(obs))) FROM (
SELECT observation_date AS date, o2_abs AS value FROM oxygen WHERE new_id = site_id) AS obs;
ELSIF var = 'o2_rel' THEN
RETURN QUERY SELECT array_to_json(array_agg(row_to_json(obs))) FROM (
SELECT observation_date AS date, o2_rel AS value FROM oxygen WHERE new_id = site_id) AS obs;
ELSIF var = 'temp' THEN
RETURN QUERY SELECT array_to_json(array_agg(row_to_json(obs))) FROM (
SELECT observation_date AS date, t AS value FROM oxygen WHERE new_id = site_id) AS obs;
END IF;
ELSIF kind = 'averaged' THEN
IF var = 'o2_abs' THEN
RETURN QUERY SELECT array_to_json(array_agg(row_to_json(obs))) FROM (
SELECT month AS month, o2_abs AS value FROM oxygen_month_average WHERE new_id = site_id) AS obs;
ELSIF var = 'o2_rel' THEN
RETURN QUERY SELECT array_to_json(array_agg(row_to_json(obs))) FROM (
SELECT month AS month, o2_rel AS value FROM oxygen_month_average WHERE new_id = site_id) AS obs;
ELSIF var = 'temp' THEN
RETURN QUERY SELECT array_to_json(array_agg(row_to_json(obs))) FROM (
SELECT month AS month, t AS value FROM oxygen_month_average WHERE new_id = site_id) AS obs;
END IF;
END IF;
END;
$func$ LANGUAGE plpgsql;

函数体包含大量重复代码。我看到了几种改进它的方法,但不知道 PL/PgSQL 是否允许这些技巧:

  1. obs查询结果存储在中间变量中,并在函数末尾使用array_to_json(array_agg(row_to_json(obs)))将其转换为JSON。
  2. var varchar 转换为查询中的列名定义,以避免大多数 IF/ELSE 语句;

PostgreSQL 服务器版本为 9.3.6。

表架构:

oxygen=# \d+ oxygen
Table "public.oxygen"
Column | Type | Modifiers (...)
-------------------------+---------+----------------------------------------------------
old_id | text | not null
observation_date_string | text | not null
t | real | not null
o2_abs | real | not null
o2_sat | real |
o2_rel | real |
observation_date | date |
new_id | integer |
id | bigint | not null default nextval('oxygen_id_seq'::regclass)
Indexes:
"oxygen_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
"oxygen_new_id_fkey" FOREIGN KEY (new_id) REFERENCES unique_geoms(new_id)
"oxygen_old_id_fkey" FOREIGN KEY (old_id) REFERENCES location(old_id)


oxygen=# \d+ oxygen_month_average
Table "public.oxygen_month_average"
Column | Type | Modifiers (...)
--------+------------------+-------------------------------------------------------------------
new_id | integer |
month | integer |
t | double precision |
o2_abs | double precision |
o2_rel | double precision |
id | bigint | not null default nextval('oxygen_month_average_id_seq'::regclass)
Indexes:
"oxygen_month_average_pkey" PRIMARY KEY, btree (id)

最佳答案

还有另一种非常有效的方法来简化其中的大部分:CASE 语句:

CREATE OR REPLACE FUNCTION get_observations(_kind    text
, _site_id int
, _var text)
RETURNS TABLE (fc json)
LANGUAGE plpgsql AS
$func$
BEGIN
CASE _kind
WHEN 'raw' THEN
RETURN QUERY
SELECT json_agg(obs) FROM (
SELECT observation_date AS date
, CASE _var
WHEN 'o2_abs' THEN o2_abs
WHEN 'o2_rel' THEN o2_rel
WHEN 'temp' THEN t
END AS value
FROM oxygen
WHERE new_id = _site_id
) AS obs;

WHEN 'averaged' THEN
RETURN QUERY
SELECT json_agg(obs) FROM (
SELECT month AS month
, CASE _var
WHEN 'o2_abs' THEN o2_abs
WHEN 'o2_rel' THEN o2_rel
WHEN 'temp' THEN t
END AS value
FROM oxygen_month_average
WHERE new_id = _site_id
) AS obs;
END CASE;
END
$func$;

fiddle
<子>旧sqlfiddle

仍然是两个不同的查询,因为它们基于不同的表。要折叠它们,您也需要使用 EXECUTE 的动态 SQL,但这不会变得更短,而且通常效率较低 ...

使用“简单”(或“转换”)CASE。请注意两个不同上下文中的 CASE 关键字:外部 CASE 是一个 PL/pgSQL 控制结构,内部 CASE is an SQL command .相关:

您可以使用 json_agg() 进一步简化喜欢@Igor already suggested :
array_to_json(array_agg(row_to_json(obs))) -> json_agg(obs)

关于sql - 使用大量 if-else 语句和 JSONification 简化 PL/pgSQL 函数,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/30252658/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com