postgresql - 如何在 Postgres 的 plv8 函数中返回 JSON 行？

Question

我无法理解这个上下文。这是我使用一些内置函数的第一次尝试:

到目前为止我尝试了什么

SELECT json_build_object('id', MD5(c.id), 'firstName', 
c.first_name, 'lastName', c.last_name, 'location', 
json_build_object('city', cl.city, 'state', cl.state))
FROM person p 
LEFT JOIN (SELECT id, city, state FROM 
person_location) pl ON pl.id = p.id
LIMIT 10

对于这个小例子来说效果很好，而且还返回 10 行。如果我取消限制，我将获得所有行。完美的;然而，这是为了支持将数据直接馈送到 Logstash 的 View ，因此有一些自定义业务逻辑可以清理字段并执行其他一些轻任务。为了解决这个问题，尝试 plv8 是有意义的

plv8方法:

CREATE OR REPLACE FUNCTION generate_search_documents()   
RETURNS SETOF person_test AS
$$

const _ = require('lodash'),
    candidates = plv8.execute('select * FROM candidate LIMIT 10');

plv8.return_next(people);

$$
  LANGUAGE plv8;

我已经迷路了。我想要做的是通过适当的连接选择我需要的表，清理数据，构建我的 JSON 对象并每行返回一个。在第一种方法中，很明显在查询中我是在一个人的上下文中工作。在下面的方法中，我似乎正在执行一个查询，该查询选择了我们表中的所有 8000 万人。这并不容易。

知道如何使用此方法来复制我在第一个示例中尝试做的事情吗？

Answer 1

示例表:

create table person(id int primary key, first_name text, last_name text);
insert into person values
(1, 'John', 'Smith'),
(2, 'Phil', 'Jones');

select 
    json_build_object(
        'id', id, 
        'firstname', first_name, 
        'lastname', last_name)
from person;

                   json_build_object                    
--------------------------------------------------------
 {"id" : 1, "firstname" : "John", "lastname" : "Smith"}
 {"id" : 2, "firstname" : "Phil", "lastname" : "Jones"}
(2 rows)

如何修改函数中的行并将连续的行作为json返回？

您应该声明setof jsonb(或setof json)作为函数的返回类型。更新值并在循环中使用 plv8.return_next()，示例:

create or replace function person_as_jsonb()
returns setof jsonb language plv8 as $$
    var persons = plv8.execute('select * from person');
    var len = persons.length;   
    for (var i = 0; i < len; i++) {
        persons[i].first_name = persons[i].first_name + '?';
        persons[i].last_name = persons[i].last_name + '!';
        plv8.return_next(persons[i]);
    }
$$;

select * 
from person_as_jsonb();

                     person_as_json                      
---------------------------------------------------------
 {"id": 1, "last_name": "Smith!", "first_name": "John?"}
 {"id": 2, "last_name": "Jones!", "first_name": "Phil?"}
(2 rows)

---

上述函数适用于非常有限的数据集。甚至不要对具有一百万行或更多行的整个表尝试它。

但是，您可以编写一个修改单行的函数。 plv8 足够聪明，可以理解何时声明参数的自定义类型(在本例中为表名)并将其用作函数内的 json (jsonb)，示例:

create or replace function modify_person(person person)
returns jsonb language plv8 as $$
    person.first_name = person.first_name+ '??';
    person.last_name = person.last_name+ '!!';
    return person;
$$;

在选择查询中使用它(Postgres 本身在这里做一个循环，分别为每一行调用该函数):

select modify_person(person)
from person;

                       modify_person                       
-----------------------------------------------------------
 {"id": 1, "last_name": "Smith!!", "first_name": "John??"}
 {"id": 2, "last_name": "Jones!!", "first_name": "Phil??"}
(2 rows)    

请注意，您只能更改返回类型以获得类型化记录的结果(在这种情况下，您应该在 from 子句中调用该函数):

create or replace function modify_person_row(person person)
returns person language plv8 as $$
    person.first_name = person.first_name+ '??';
    person.last_name = person.last_name+ '!!';
    return person;
$$;

select modified.*
from person,
lateral modify_person_row(person) modified

 id | first_name | last_name 
----+------------+-----------
  1 | John??     | Smith!!
  2 | Phil??     | Jones!!
(2 rows)