我必须创建一个接受此类型对象的函数:
"users":"John",
"Level":"1",
"list":[
{"id":"1", "price1":"100.50", "price2":"90.50"},
{"id":"2", "price1":"100.60", "price2":"80.50"},
{"id":"2", "price1":"105.50", "price2":"700.50"}
]}
作为参考,JSON不是强制性的,但是对我而言,这似乎是最简单的事情。但是也许可以创建POSTGRES类型。
然后,如果我使用JSON或PostGres类型,该如何处理对象。
CREATE OR REPLACE FUNCTION MYFUNCTION(myobject JSON ? CREATE TYPE ? )
RETURNS TABLE (
"OK" BOOLEAN,
"NB" VARCHAR
)
AS $$
DECLARE
BEGIN
-- I would like to get for each object in the list the price1 and price2 to
compare them.
END; $$
LANGUAGE 'plpgsql';
问题的症结在于如何从json对象中提取值。这是一种方法:
select * from json_to_recordset('{
"users":"John",
"Level":"1",
"list":[
{"id":"1", "price1":"100.50", "price2":"90.50"},
{"id":"2", "price1":"100.60", "price2":"80.50"},
{"id":"2", "price1":"105.50", "price2":"700.50"}
]}'::json->'list') as foo(id int, price1 numeric, price2 numeric);
使用json变量而不是文字字符串:
select * from json_to_recordset(jsonvariable->'list') as foo(id int, price1 numeric, price2 numeric)
注意。您提供的json对象不合法。缺少一些逗号。我还建议您使用jsonb而不是json。
编辑:这是如何在plpgsql函数中使用它的框架:
create or replace function func(jsonvariable json) returns table (ok boolean, nb text) as
$BODY$
declare
r record;
begin
for r in (select * from json_to_recordset(jsonvariable->'list') as foo(id int, price1 numeric, price2 numeric)) loop
--- DO THINGS
ok:=(r.price1>r.price2);
nb:='this is text returned';
return next;
end loop;
end;
$BODY$
language plpgsql;
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句