0

I'll start off by stating that I haven't had to use SQL in some time and although I've written SQL queries,I haven't had the chance to use embedded functions which is what I'm trying to do here...

I have a query which by itself produces several rows of data and I use the row_to_json function to format that data for use in a rest api. I currently have the query hard coded in a node.js implementation but would like to move it into the Db and simply call a function. I've managed to write the function ** BUT ** it only returns the first row of data. I'm certain I'm simply misunderstanding some simple concept but my Googling hasn't helped me determine what is the right course of action. Please review the following and let me know why the function only returns the first record.

SELECT row_to_json(FooBar) FROM(SELECT car_items_t.item_id, car_items_t.item_name, 
car_items_t.item_desc, car_items_t.item_photo, car_items_t.item_price 
FROM "SS".rfid_t, "SS".tr_t, "SS".car_t, 
"SS".car_items_t, "SS".tr_items_t 
WHERE rfid_t.aid = 1 
AND rfid_t.tid = tr_t.tid AND tr_t.aid = car_t.aid 
AND tr_t.tid = tr_items_t.tid AND car_t.mid = car_items_t.mid 
AND tr_items_t.mid = car_t.mid) FooBar;

VS.

CREATE OR REPLACE FUNCTION "SS".getitemsbyrfid(integer)
  RETURNS json AS
$BODY$ 

SELECT row_to_json(FooBar) FROM(SELECT car_items_t.item_id, car_items_t.item_name, 
car_items_t.item_desc, car_items_t.item_photo, car_items_t.item_price 
FROM "SS".rfid_t, "SS".tr_t, "SS".car_t, 
"SS".car_items_t, "SS".tr_items_t 
WHERE rfid_t.aid = $1 
AND rfid_t.tid = tr_t.tid AND tr_t.aid = car_t.aid 
AND tr_t.tid = tr_items_t.tid AND car_t.mid = car_items_t.mid 
AND tr_items_t.mid = car_t.mid) FooBar;

$BODY$

1 Answer 1

2

It's declared RETURNS json.

If you want a set, declare it RETURNS SETOF json.

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.