CODEWITHSUNDEEP
sqlfunctionpostgresqlplpgsql
Alex Wechsler
Alex Wechsler

Reputation: 1

Using row_to_json with PL/pgSQL Create Function?

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$

Upvotes: 0

Views: 359

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324771

It's declared RETURNS json.

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

Upvotes: 2

Related Questions