如何在postgresql函数中返回查询

Question

我有PostgreSQL函数与json数据对象，我需要返回一些值这是我的函数

CREATE OR REPLACE FUNCTION "public"."insert_from_json"("in_json_txt" json)
RETURNS "pg_catalog"."void" AS $BODY$
INSERT INTO json_test2 (name, age, location_id)
WITH t1 AS (
SELECT (rec->>'name')::text , (rec->>'age')::integer  FROM
json_array_elements(in_json_txt->'data') rec

),t2 AS (
WITH my_v_table ( jsonblob ) AS ( VALUES ( in_json_txt:: jsonb ) ) 

SELECT
((my_v_table.jsonblob ->> 'Store_IntegrationCode')::numeric) as store_id
FROM my_v_table
)

SELECT * from t1,t2

$BODY$
LANGUAGE sql VOLATILE
COST 100

当我使用返回查询时，我得到了错误:(

这是call语句

select insert_from_json('{
"Customer_IntegrationCode": "558889999",
"XretialOrderCode": "000020430",
"ShippingAddress": "Cairo, Nasr City, 01128777733",
"ShippingAddress_IntegrationCode": null,
"PaymentOption": 1,
"CreationDate": "2021-01-04T07:38:57.033Z",
"Total": 73.0,
"Currency": "EGP",
"Note": null,
"ShippingCost": 15.0,
"CODFee": 25.0,
"ShipmentProvider": null, 
"Plateform": 1, 
"SubTotal": 33.0,
"TotalDiscountAmount_PerOrderLevel": 0,
"OriginalSubTotal": 33.0,
"TaxPercentage": null,
"TaxValue": null,
"Store_IntegrationCode": "1234567",
"data": [
{
"name": "12345678",
"age": "23456789",
"Qty": 3,
"UnitPrice": 11.0,
"NetPrice": 11.0,
"SKUDiscount": 0,
"Total": 33.0,
"ShipmentCost": 0.0,
"SubTotal": 33.0

},
{
"name": "999999",
"age": "988888",
"Qty": 3,
"UnitPrice": 11.0,
"NetPrice": 11.0,
"SKUDiscount": 0,
"Total": 33.0,
"ShipmentCost": 0.0,
"SubTotal": 33.0

}
]
}
')

当我添加返回查询函数时，我得到了这个错误

ERROR: "RETURN"第18行:返回查询SELECT * from t1,t2

Answer 1

不能返回返回VOID的函数的值。如果您想在插入后返回行，您可以尝试下面提到的函数定义:

CREATE OR REPLACE FUNCTION "public"."insert_from_json"("in_json_txt" json)
RETURNS 
table (name_ text, age_ int, location_ numeric)
AS $BODY$
BEGIN
RETURN QUERY
INSERT INTO json_test2 (name, age, location_id)
(WITH t1 AS (
SELECT (rec->>'name')::text , (rec->>'age')::integer  FROM
json_array_elements(in_json_txt->'data') rec
),t2 AS (
WITH my_v_table ( jsonblob ) AS ( VALUES ( in_json_txt:: jsonb ) ) 

SELECT
((my_v_table.jsonblob ->> 'Store_IntegrationCode')::numeric) as store_id
FROM my_v_table
)
SELECT * from t1,t2
)
RETURNING name, age, location_id;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100

演示同样的东西，你也可以写得更短更简单，像下面这样:

CREATE OR REPLACE FUNCTION "public"."insert_from_json"("in_json_txt" json)
RETURNS 
table (name_ text, age_ int, location_ numeric)
AS $BODY$
BEGIN
RETURN QUERY
INSERT INTO json_test2 (name, age, location_id)
SELECT 
(rec->>'name')::text, (rec->>'age')::integer, (in_json_txt->>'Store_IntegrationCode')::numeric   
FROM json_array_elements(in_json_txt->'data') rec
RETURNING name, age, location_id;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100

演示

Answer 2

您可以将函数简化为一个"Insert…Returning"通过'转换'到SQL函数

create or replace function insert_from_json(in_json_txt json)
returns setof json_test2
language sql 
as $$
insert into json_test2 (name, age, location)
select (rec->>'name')::text
, (rec->>'age')::integer
, (in_json_txt->>'Store_IntegrationCode')::numeric   
from json_array_elements(in_json_txt->'data') rec
returning *;
$$; 

看例子