Tengo la siguiente tabla con dos campos:
create table tbl_jtest ( cola int, colb varchar(10) );Insertando algunos registros:
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');Función :
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;Detalles del error :
ERROR: no se puede usar RETURN QUERY en una función que no sea SETOF LÍNEA 7: RETURN QUERY SELECT to_json(a.cola) FROM tbl_jtest a; ^
He probado lo siguiente:
Prueba 1 :
PERFORM to_json(a.cola) FROM tbl_jtest a;Prueba 2 :
RETURN QUERY PERFORM to_json(a.cola) FROM tbl_jtest a;¿O estás buscando algo como esto?
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;Prueba 1
SELECT ufn_jtest1(1);Salida 1
ufn_jtest1 1 {"cola":1,"colb":"a"} 2 {"cola":2,"colb":"b"} 3 {"cola":3,"colb":"c"} 4 {"cola":4,"colb":"d"}Prueba2
SELECT ufn_jtest1(2);Salida2 ufn_jtest1
1 {"colb":"a","colc":"e"} 2 {"colb":"b","colc":"f"} 3 {"colb":"c","colc":"g"} 4 {"colb":"d","colc":"h"}¿Estás buscando algo así?:
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;