时间戳

首页 > 解决方案 > 如何为 postgresql 中的查询创建辅助函数？

postgresql - 如何为 postgresql 中的查询创建辅助函数？

问题描述

我有一个类似的查询：

SELECT COALESCE(
    (SELECT jsonb_agg(s) FROM ((SELECT column FROM my_table)) AS s),
    '[]'::jsonb
);

它以 json 格式返回所有行：

[
    {
        "column": 1,
    },
    {
        "column": 2
    }
]

如果没有行，它会返回一个空数组而不是null.

我想重新使用它，但是在任何地方都输入整个内容很快就会变得一团糟。这就是我尝试创建此as_json功能的原因：

-- DOESNT WORK
CREATE FUNCTION as_json(query ???)
    RETURNS jsonb
    LANGUAGE sql
AS $$
    SELECT COALESCE((SELECT jsonb_agg(s) FROM query AS s), '[]'::jsonb);
$$;

如果我能让它工作，使用它就像

as_json(SELECT column FROM my_table)

我可以在 postgresql 中实现类似的东西吗？

标签： postgresql

解决方案

实际上很容易写出类似的东西query_to_xml()

create or replace function query_to_jsonb(p_query text, p_include_nulls boolean default false)
  returns jsonb
as
$$
declare
  l_result jsonb;
  l_sql text;
begin
  if p_include_nulls then 
    l_sql := 'select jsonb_agg(to_jsonb(dt)) from ('||p_query||') as dt';
  else 
    l_sql := 'select jsonb_agg(jsonb_strip_nulls(to_jsonb(dt))) from ('||p_query||') as dt';
  end if;
  
  execute l_sql
    into l_result;
    
  return coalesce(l_result, '{}');
end;
$$
language plpgsql;

然后您可以将查询结果（字符串）作为 JSON 值返回，例如

select query_to_jsonb('SELECT column FROM my_table');

要从结果中删除 NULL 值，请使用：

select query_to_jsonb('select col1, col2 from some_table', true);

推荐阅读