PostgreSQL: Return JSON from function using language plpgsql

Question

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;

RETURN (SELECT to_json(a.cola) FROM tbl_jtest a); - what is you r goal anyway?.. — Vao Tsun
– Vao Tsun, Commented Apr 20, 2017 at 7:25
Syntax-wise @VaoTsun is right, but the fact that you do not filter the tbl_jtest table suggests that you indeed want a RETURNS SETOF json function instead (like PostgreSQL suggests). If the table contains multiple rows, a RETURNS json function will fail (unless you use a WHERE and/or a LIMIT 1 clause). — pozs
– pozs, Commented Apr 20, 2017 at 7:30
@pozs, Want to return only specific columns from the table which is based on the condition. — MAK
– MAK, Commented Apr 20, 2017 at 7:31
@pozs, There is also condition were I want to specify more than one columns like cola,colb in select statement. — MAK
– MAK, Commented Apr 20, 2017 at 7:38

etsa · Accepted Answer · 2017-04-20 08:05:42Z

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"}

Vao Tsun · Accepted Answer · 2017-04-20 07:47:42Z

3

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;

answered Apr 20, 2017 at 7:47

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

Collectives™ on Stack Overflow

PostgreSQL: Return JSON from function using language plpgsql

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related