Reputation: 523
How to perform a query as argument in PL/pgSQL function which returns JSON?
I'm trying to perform select by using already created function which returns JSON which aggregates into JSON array, but I'm getting problem in line where I'm performing a SELECT-clause. Tables I have problem querying:
create table orders (
order_id bigserial not null, -- this is Primary Key
total double precision,
order_date timestamp,
user_id bigint -- references `users` table
);
create table order_item
(
order_item_id bigserial not null, --primary key
amount integer,
book_id bigint, -- FK which references `book` table
order_id bigint -- FK which references `orders` table
);
My query looks like this in PL/pgSQL:
create or replace function public.get_order_by_order_id(o_id bigint) returns json as
$BODY$
DECLARE
order_items json;
found_order "vertx-jooq-cr".public.orders;
found_user json;
begin
-- other queries left out for code brevity
select json_agg(x) INTO order_items
from (select public.get_orderitem_by_oi_id(
select oi.order_item_id -- this is where PROBLEM occurs !!!
from public.order_item AS oi
where oi.order_id = o_id)
) x;
return (select json_build_object(
'order_id', found_order.order_id,
'total_price', found_order.total,
'order_date', found_order.order_date,
'user', found_user,
'order_items', order_items
));
end
$BODY$
language 'plpgsql';
...and here is function get_orderitem_by_oi_id(order_item_id::bigint) (which works properly and returns JSON):
In line where "-- this is where PROBLEM occurs !!!" comment is placed I get an error/warning in DataGrip which states:
')', or ORDER expected, got 'select'
This is how my JSON should look like:
{
"order_id": 21,
"total_price": 89.92,
"order_date": "2020-05-03 00:00:00",
"order_items": [
{
"order_item_id": 32,
"amount": 3,
"book": {
"book_id": 2,
"title": "Murder on the Orient Express",
"price": 19.98,
"amount": 151,
"deleted": false,
"authors": [
{
"author_id": 1,
"first_name": "Agatha",
"last_name": "Christie"
}
],
"categories": [
{
"category_id": 9,
"name": "Crime",
"deleted": false
}
]
},
"order_id": 21,
"total_order_item_price": 59.94
},
{
"order_item_id": 31,
"amount": 2,
"book": {
"book_id": 5,
"title": "Harry Potter and the Prisoner of Azkaban",
"price": 14.99,
"amount": 85,
"deleted": false,
"authors": [
{
"author_id": 4,
"first_name": "JK",
"last_name": "Rowling"
}
],
"categories": [
{
"category_id": 3,
"name": "Tragedy",
"deleted": false
}
]
},
"order_id": 21,
"total_order_item_price": 29.98
}
],
"user": {
"user_id": 1,
"username": "test"
}
}
My question is is it possible to query for adequate order_item_id (from order_item table) using get_orderitem_by_oi_id(order_item_id::bigint) function by passing adequate order_id in query and if not this way is there ANY other suitable way to achieve this?
Any kind of help in solving this problem is greatly appreciated.
P.S. Version of PostgreSQL is 11.8
UPDATE1:
I've edited my Pl/pgSQL function and now looks like this:
create or replace function public.get_order_by_order_id(o_id bigint) returns json as $BODY$
DECLARE
order_items json;
found_order "vertx-jooq-cr".public.orders;
found_user json;
_item_id bigint; -- left it here from @Adrian Klaver's 1st version answer
_oitems_ids bigint[];
item_recs RECORD;
begin
-- other necessary queries left out for code brevity
FOR item_recs IN SELECT oi.order_item_id into _item_id -- gives an error
FROM public.order_item AS oi WHERE oi.order_id = o_id
LOOP
--- Will need to modify to get your final JSON structure.
SELECT json_agg(x) INTO order_items
FROM (SELECT public.get_orderitem_by_oi_id(item_recs.order_item_id)) x;
END LOOP;
return (select json_build_object(
'order_id', found_order.order_id,
'total_price', trunc(found_order.total::double precision::text::numeric, 2),
'order_date', found_order.order_date,
'user', found_user,
'order_items', item_recs -- updated value from 'order_items'
));
end
$BODY$
language 'plpgsql';
Error I get when I execute function is:
ERROR: cannot open SELECT query as cursor
CONTEXT: PL/pgSQL function get_order_by_order_id(bigint) line 21 at FOR over SELECT rows SQL
state: 42P11
Unfortunately I don't have experience with cursors in PostgreSQL and PL/pgSQL (started learning PL/pgSQL less than week ago). Any idea what this error points to and how should this be fixed?
Thank you in advance.
UPDATE2: I've edited my query (FOR-LOOP part to be more precise), executed it and got this as result:
{
"order_id": 1069,
"total_price": 136.94,
"order_date": "2020-06-10T19:57:40.562",
"user": 3,
"order_items": {
"order_item_id": 2042
}
}
BTW, order_id has two order_items with order_item_id 2041 and 2042 which means that only 2nd order_item is being "caught". Also, not full JSON object is built from public.get_orderitem_by_oi_id(oi_id **bigint**) function. Any advice how to fix this?
UPDATE3:
By made comments I have to stress that public.get_orderitem_by_oi_id(_item_id) function RETURNS JSON type and here is my updated function which looks like this now:
create or replace function public.get_order_by_order_id6(o_id bigint) returns json as $BODY$
DECLARE
order_items json;
found_order "vertx-jooq-cr".public.orders;
found_user json;
_item_id bigint;
_oitems_ids bigint[];
item_recs RECORD;
begin
select * into found_order
from "vertx-jooq-cr".public.orders
where order_id = o_id;
-- other queries left out for code brevity
FOR _item_id IN SELECT DISTINCT oi.order_item_id
FROM public.order_item AS oi WHERE oi.order_id = o_id
LOOP
--- Will need to modify to get your final JSON structure.
SELECT json_agg(x) INTO order_items
FROM (SELECT public.get_orderitem_by_oi_id(_item_id)) x;
END LOOP;
return (select json_build_object(
'order_id', found_order.order_id,
'total_price', trunc(found_order.total::double precision::text::numeric, 2),
'order_date', found_order.order_date,
'user', found_user,
'order_items', order_items
));
end
$BODY$
language 'plpgsql';
...and this is result I get (in JSON of course):
{
"order_id": 1069,
"total_price": 136.93,
"order_date": "2020-06-10T19:57:40.562",
"user": {
"user_id": 3,
"username": "mica"
},
"order_items": [
{
"get_orderitem_by_oi_id": { -- for some reason it INSERTS function name HERE!!!
"order_item_id": 2042,
"amount": 2,
"book": {
"book_id": 8,
"title": "The Lord of the Rings",
"price": 23.5,
"amount": 298,
"is_deleted": false,
"authors": [
{
"author_id": 3,
"first_name": "JRR",
"last_name": "Tolkien"
}
],
"categories": [
{
"category_id": 9,
"name": "Crime",
"is_deleted": false
}
]
},
"order_id": 1069,
"total_order_item_price": 59.96
}
}
]
}
For some reason it keeps fetching ONLY LAST record of selected order_itemS and keeps inserting function name it result (stated under "-- for some reason it INSERTS function name HERE!!!" comment in JSON code). Any idea how to aggregate/collect ALL records into order_items json variable?
P.S. I've experimented in separate function with FOR-LOOP where variables are same (except order_items which is of type json[]):
FOR _item_id IN SELECT DISTINCT oi.order_item_id FROM public.order_item AS oi WHERE oi.order_id = o_id
LOOP
order_items := order_items || json_build_object('order_item_id', _item_id);
END LOOP;
..and it gave ALL IDs for order_item (2041 and 2042, NOT just 2042 like get_order_by_order_id6() function).
UPDATE4:
Here is function get_orderitem_by_oi_id(order_item_id::bigint) (which works properly and returns JSON):
create or replace function get_orderitem_by_oi_id(oi_id bigint) returns json
language plpgsql
as
$FUNCTION$
declare
found_oi "vertx-jooq-cr".public.order_item;
book_json json;
total_oi_price decimal;
book_price double precision;
begin
select * into found_oi
from public.order_item AS oi2
where oi2.order_item_id = oi_id;
select public.get_book_by_book_id(public.order_item.book_id::bigint) into book_json
from public.order_item
where public.order_item.order_item_id = oi_id;
select price into book_price
from book AS b
inner join public.order_item AS oi USING (book_id);
total_oi_price = found_oi.amount * book_price;
return (select json_build_object(
'order_item_id', found_oi.order_item_id,
'amount', found_oi.amount,
'book', book_json,
'order_id', found_oi.order_id,
'total_order_item_price', trunc(total_oi_price::double precision::text::numeric, 2)
));
end
$FUNCTION$;
Upvotes: 0
Views: 743
Answers (3)
Reputation: 523
After taking useful advises from @AdrianKlaver I came up to a solution by declaring additional necessary variables to handle each order_item in FOR-LOOP and queries to produce a final (needed) result. Here's the final solution which works as it should (made changes by @AdrianKlaver's 2nd answer):
create or replace function public.get_order_by_order_id8(o_id bigint) returns json as
$BODY$
DECLARE
total_oi_price double precision;
book_price double precision;
total_price double precision;
oi_amount integer;
order_items json;
item_recs RECORD;
book_json json;
single_order_item json;
found_order public.orders;
found_user json;
item_array json[];
BEGIN
select * into found_order
from public.orders
where order_id = o_id;
select json_build_object('user_id', public.users.user_id, 'username', public.users.username)
into found_user
from public.users
INNER JOIN public.orders as o USING (user_id)
WHERE o.order_id = o_id;
total_price = 0.00;
FOR item_recs IN SELECT *
FROM public.order_item AS oi WHERE oi.order_id = o_id
LOOP
select public.get_book_by_book_id(item_recs.book_id) into book_json;
select price INTO book_price FROM book AS b WHERE b.book_id = item_recs.book_id;
oi_amount = item_recs.amount;
total_oi_price = book_price * oi_amount;
SELECT json_build_object('order_item_id', item_recs.order_item_id,
'amount', item_recs.amount,
'book', book_json,
'order_id', item_recs.order_id,
'total_order_item_price', trunc(total_oi_price::double precision::text::numeric, 2)) INTO single_order_item;
total_price := total_price + total_oi_price;
item_array = array_append(item_array, single_order_item);
END LOOP;
order_items = array_to_json(item_array);
return (select json_build_object(
'order_id', found_order.order_id,
'total_price', trunc(total_price::double precision::text::numeric, 2),
'order_date', found_order.order_date,
'user', found_user,
'order_items', order_items
));
end;
$BODY$
LANGUAGE 'plpgsql';
...and here is the JSON result I get:
{
"order_id": 1069,
"total_price": 136.94,
"order_date": "2020-06-10T19:57:40.562",
"user": {
"user_id": 3,
"username": "mica"
},
"order_items": [
{
"order_item_id": 2041,
"amount": 3,
"book": {
"book_id": 6,
"title": "The Da Vinci Code",
"price": 29.98,
"amount": 297,
"is_deleted": false,
"authors": [
{
"author_id": 8,
"first_name": "William",
"last_name": " Shakespeare"
}
],
"categories": [
{
"category_id": 10,
"name": "Action",
"is_deleted": false
}
]
},
"order_id": 1069,
"total_order_item_price": 89.94
},
{
"order_item_id": 2042,
"amount": 2,
"book": {
"book_id": 8,
"title": "The Lord of the Rings",
"price": 23.5,
"amount": 298,
"is_deleted": false,
"authors": [
{
"author_id": 3,
"first_name": "JRR",
"last_name": "Tolkien"
}
],
"categories": [
{
"category_id": 9,
"name": "Crime",
"is_deleted": false
}
]
},
"order_id": 1069,
"total_order_item_price": 47
}
]
}
It turns out that get_orderitem_by_oi_id() was sufficient and it needed result can be done without it.
Upvotes: 1
Reputation: 19570
What I see in your answer. This:
from public.order_item
where public.order_item.order_item_id IN (item_recs.order_item_id);
should be shortened to this:
select public.get_book_by_book_id(item_recs.book_id) into book_json;
as you are already on a unique record.
This:
select amount INTO oi_amount FROM order_item AS oi WHERE oi.amount = item_recs.amount;
should be:
oi_amount = item_recs.amount;
because the query might select more then one amount from order_item if there are more then one record with a oi.amount = item_recs.amount. Also you are saying item_recs.amount is the amount you are looking for anyway.
Upvotes: 1
Reputation: 19570
You can't pass a query into a function as an argument. What you can do is something like:
DECLARE
order_items json;
found_order "vertx-jooq-cr".public.orders;
found_user json;
item_recs RECORD;
BEGIN
FOR item_recs IN select oi.order_item_id
from public.order_item AS oi where oi.order_id = o_id
LOOP
--- Will need to modify to get your final JSON structure.
select json_agg(x) INTO order_items
from (select public.get_orderitem_by_oi_id(item_recs.order_item_id)
) x;
END LOOP;
...
The above comes from here:
https://www.postgresql.org/docs/12/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING
Alternate method, without using LOOP:
create table orders (
order_id bigserial not null, -- this is Primary Key
total double precision,
order_date timestamp,
user_id bigint -- references `users` table
);
create table order_item
(
order_item_id bigserial not null, --primary key
amount integer,
book_id bigint, -- FK which references `book` table
order_id bigint -- FK which references `orders` table
);
insert into orders (total, order_date, user_id) values (100, '06/20/2020', 1);
insert into orders (total, order_date, user_id) values (250, '06/20/2020', 2);
insert into order_item values (1, 45, 1, 1);
insert into order_item values (2, 55, 1, 1);
insert into order_item values (3, 50, 3, 2);
insert into order_item values (4, 100, 3, 2);
insert into order_item values (5, 75, 3, 2);
insert into order_item values (6, 25, 3, 2);
CREATE OR REPLACE FUNCTION public.get_order_by_order_id(o_id bigint)
RETURNS json
LANGUAGE plpgsql
AS $function$
DECLARE
order_items json;
found_order RECORD;
item_recs RECORD;
BEGIN
SELECT
order_id, order_date, total, user_id
INTO
found_order
FROM
orders
WHERE
order_id = o_id;
SELECT
json_agg(row_to_json(x))
INTO
order_items
FROM
(SELECT
*
FROM
order_item
WHERE order_id = o_id) AS x;
RETURN (select json_build_object(
'order_id', found_order.order_id,
'total_price', trunc(found_order.total::double precision::text::numeric, 2),
'order_date', found_order.order_date,
'user', found_order.user_id,
'order_items', order_items
));
END;
$function$
;
select * from get_order_by_order_id (1);
{"order_id" : 1,
"total_price" : 100.00,
"order_date" : "2020-06-20T00:00:00",
"user" : 1,
"order_items" : [{"order_item_id":1,"amount":45,"book_id":1,"order_id":1}{"order_item_id":2,"amount":55,"book_id":1,"order_id":1}]}
UPDATE 06/22/2020 with array. This is a generic example as I do not have time to build your entire setup, but I believe it should show you what needs to be done. In your case capture the output of get_orderitem_by_oi_id() to add to the array. In the below order_item is a test table I set up to hold the two items from 'order_items' above. The rest up the data are dummy values. So:
CREATE OR REPLACE FUNCTION public.json_array_example(o_id bigint)
RETURNS json
LANGUAGE plpgsql
AS $function$
DECLARE
order_items json;
item_recs RECORD;
item_array json[];
BEGIN
FOR item_recs IN SELECT order_item FROM hold_json
LOOP
item_array = array_append(item_array, item_recs.order_item);
END LOOP;
order_items = array_to_json(item_array);
RETURN (select json_build_object(
'order_id', o_id,
'total_price', trunc(100::double precision::text::numeric, 2),
'order_date', '06/22/2020',
'user', 2,
'order_items', order_items
));
END;
$function$
Output:
json_array_example
------------------------------------------------------------------------------------------------------
{"order_id" : 1, "total_price" : 100.00, "order_date" : "06/22/2020", "user" : 2, "order_items" : [{+
"order_item_id": 32, +
"amount": 3, +
"book": { +
"book_id": 2, +
"title": "Murder on the Orient Express", +
"price": 19.98, +
"amount": 151, +
"deleted": false, +
"authors": [ +
{ +
"author_id": 1, +
"first_name": "Agatha", +
"last_name": "Christie" +
} +
], +
"categories": [ +
{ +
"category_id": 9, +
"name": "Crime", +
"deleted": false +
} +
] +
}, +
"order_id": 21, +
"total_order_item_price": 59.94 +
},{ +
"order_item_id": 31, +
"amount": 2, +
"book": { +
"book_id": 5, +
"title": "Harry Potter and the Prisoner of Azkaban", +
"price": 14.99, +
"amount": 85, +
"deleted": false, +
"authors": [ +
{ +
"author_id": 4, +
"first_name": "JK", +
"last_name": "Rowling" +
} +
], +
"categories": [ +
{ +
"category_id": 3, +
"name": "Tragedy", +
"deleted": false +
} +
] +
}, +
"order_id": 21, +
"total_order_item_price": 29.98 +
}]}
Upvotes: 1
Related Questions
- Declare and return a json variable with postgresql function
- Processing results from plpgsql function
- Return JSON array with postgres functions
- How do you return rows of JSON in a plv8 function in Postgres?
- How do I pass Postgres function param into a JSON query?
- Psql function with array of json
- How to pass json array to postgresql function
- Returning SELECT results from a function as a JSON (Postgres)
- PostgreSQL Function: Return Multiple Rows as JSON
- How do I pass in the array output of SQL query into a PostgreSQL (PL/pgSQL) function?