0

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 enter image description here

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.

chintuyadavsara
  • 1,509
  • 1
  • 12
  • 23

0 Answers0