I have the following table with two fields:
create table tbl_jtest
(
cola int,
colb varchar(10)
);
Inserting some records:
insert into tbl_jtest values(1,'a');
insert into tbl_jtest values(2,'b');
insert into tbl_jtest values(3,'c');
insert into tbl_jtest values(4,'d');
Function:
CREATE OR REPLACE FUNCTION ufn_jtest1(pcola int)
RETURNS json AS
$$
BEGIN
IF pcola = 1
THEN
RETURN QUERY SELECT to_json(a.cola) FROM tbl_jtest a;
ELSE
RETURN QUERY SELECT to_json(a.colb) FROM tbl_jtest a;
END IF;
END;
$$ LANGUAGE plpgsql;
Error details:
ERROR: cannot use RETURN QUERY in a non-SETOF function LINE 7: RETURN QUERY SELECT to_json(a.cola) FROM tbl_jtest a; ^
I have tried the followings:
Try 1:
PERFORM to_json(a.cola) FROM tbl_jtest a;
Try 2:
RETURN QUERY PERFORM to_json(a.cola) FROM tbl_jtest a;
Or are you looking for something like this?
create table tbl_jtest
(
cola int,
colb varchar(10),
colc varchar(10)
);
insert into tbl_jtest values(1,'a','e');
insert into tbl_jtest values(2,'b','f');
insert into tbl_jtest values(3,'c','g');
insert into tbl_jtest values(4,'d','h');
SELECT * FROM tbl_jtest;
CREATE OR REPLACE FUNCTION ufn_jtest1(pcola int)
RETURNS table (j json) AS
$$
BEGIN
IF pcola = 1
THEN
RETURN QUERY SELECT row_to_json(a) FROM (SELECT cola, colb FROM tbl_jtest) a;
ELSE
RETURN QUERY SELECT to_json(a) FROM (SELECT colb, colc FROM tbl_jtest) a;
END IF;
END;
$$ LANGUAGE plpgsql;
Test 1
SELECT ufn_jtest1(1);
Output 1
ufn_jtest1
1 {"cola":1,"colb":"a"}
2 {"cola":2,"colb":"b"}
3 {"cola":3,"colb":"c"}
4 {"cola":4,"colb":"d"}
Test2
SELECT ufn_jtest1(2);
Output2 ufn_jtest1
1 {"colb":"a","colc":"e"}
2 {"colb":"b","colc":"f"}
3 {"colb":"c","colc":"g"}
4 {"colb":"d","colc":"h"}
are loking for such?:
CREATE OR REPLACE FUNCTION ufn_jtest2(pcola int)
RETURNS table (j json) AS
$$
BEGIN
IF pcola = 1
THEN
RETURN QUERY SELECT to_json(a.cola) FROM tbl_jtest a;
ELSE
RETURN QUERY SELECT to_json(a.colb) FROM tbl_jtest a;
END IF;
END;
$$ LANGUAGE plpgsql;