I'm trying to write a function which executes a dynamically prepared sql query and return the result as table.
I was refering to the SO answer, it mentioned to use language plpgsql
, even after using it I'm getting the same syntax error.
Below is the function code provided.
CREATE OR REPLACE FUNCTION public.execute_test(
ddsmappingids text,
totalnumberofrecords bigint,
skiprecords bigint,
pagesize integer,
cid bigint)
RETURNS TABLE(sampletime timestamp without time zone, jsonstring text, rowscount bigint)
LANGUAGE 'plpgsql'
COST 100
VOLATILE PARALLEL UNSAFE
ROWS 1000
AS $BODY$
DECLARE
table_names text:='dynamictable';
sql_query text;
dsids_array int[];
sub_query text;
BEGIN
if cid>=2 then
select 'dynamictable_'||cid into table_names;
end if;
select string_to_array(ddsmappingids, ',')::int[] into dsids_array;
sub_query:='SELECT * , COUNT(*) over() AS row_count FROM (SELECT start_time, cast(jsonb_object_agg(vw.dataseries_name, ROUND(CAST(o.dbl_value as numeric), 4)) as text) AS DataSeriesValue
FROM ' || quote_ident(table_names) ||' o
join vwdds vw on o.ddmid=vw.id
where ddmid in (' || array_to_string(dsids_array,',') || ')
and o.dbl_value is not null
GROUP BY start_time
ORDER BY start_time desc
limit ' || totalnumberofrecords || ') t offset '|| skiprecords || ' rows fetch next ' || pagesize || ' rows only;';
RAISE NOTICE 'Temporary table created';
RETURN QUERY Execute sub_query;
END
$BODY$;
Error Message when running the function
EXECUTE
command, when i try with some simple query it is working fine, but the query mentioned in the code snippet it is giving syntax error.
Please help me where I'm doing wrong.