如何在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的视图，因此有一些自定义业务逻辑可以清理字段并执行一些其他轻松任务。为了补救这个问题，尝试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)

上述功能将在非常有限的数据集中很好地工作。甚至不要对包含一百万行或更多行的整个表进行尝试。

但是，您可以编写修改单个行的函数。当您声明参数的自定义类型(本例中的表名)并将其用作函数中的json (jsonb)时，plv8非常聪明，例如：

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;
$$;

在select查询中使用此方法(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)