I am trying to write a store procedure where I pass a query into a stored procedure which will internally call the row_to_json function. We have multiple DBMS (Oracle, SQL Server & postgres) and want to write a generic procedure which will act as a wrapper for the json handling function.
I am looking at something like this
CREATE OR REPLACE FUNCTION proj.sql_row_to_json(sql_t text)
RETURNS json
LANGUAGE sql
AS $function$
with t as (select sql_t )
select row_to_json(t) from t;
$function$
I would call function in my application as
select SQL_ROW_TO_JSON('<sql query>') -> which should work across different databases
But the above procedure gives me the below result instead of the actual data.
select SQL_ROW_TO_JSON('SELECT id,name FROM emp')
{"sql_t":"SELECT id,name FROM emp"}
You have to use plpgsql and dynamic sql.
create or replace function sql_row_to_json(sql_t text)
returns json language plpgsql as $function$
declare rslt json;
begin
execute
format($ex$
select json_agg(row_to_json(t))
from (%s) t
$ex$, sql_t)
into rslt;
return rslt;
end;
$function$;
select sql_row_to_json('select 1 as id, ''abcd'' as value');
sql_row_to_json
-------------------------
{"id":1,"value":"abcd"}
(1 row)
Note that the function is prone to SQL-injection attacks.