postgresql dynamic query-CodePudding

I need to replace schema and table name by parameters in following function (that is currently working perfectly):

CREATE OR REPLACE FUNCTION public.my_function_119()
 RETURNS integer
 LANGUAGE plpgsql
AS $function$
                DECLARE _check INTEGER;
                BEGIN
                    SELECT SUM("length"/1000)
                    FROM public."National_Grid_multiline"
                    INTO _check;
                    RETURN _check;
                END
                $function$

I have tried following solution (and its numerous variations) :

CREATE OR REPLACE FUNCTION public.my_function_119(schema text, tablename text)
 RETURNS INTEGER
 LANGUAGE  plpgsql
AS
$function$
                DECLARE _check INTEGER;
                BEGIN
                  RETURN
                    '(SELECT SUM((length/1000))::integer FROM ' || schema || '."' || tablename || '")::integer INTO _check' ;
                RETURN _check;
                END
                $function$

but keep running into following error code :

psycopg2.errors.InvalidTextRepresentation: invalid input syntax for type integer: "(SELECT SUM((length/1000))::integer FROM public."National_Grid_multiline")::integer INTO _check"

CONTEXT:  PL/pgSQL function my_function_119(text,text) while casting return value to function's return type

Why is this not working ? The 'length' column contains float values.

CodePudding user response：

You have to use dynamic SQL, because you cannot use a parameter for an identifier.

Also, make sure to avoid SQL injection by using format rather than concatenating strings:

EXECUTE
   format(
      '(SELECT SUM((length/1000))::integer FROM %I.%I',
      schema,
      table_name
   )
   INTO _check';

CodePudding user response：

You can try this :

CREATE OR REPLACE FUNCTION public.my_function_119(schema text, tablename text)
 RETURNS INTEGER
 LANGUAGE  plpgsql
AS
$function$
BEGIN
RETURN EXECUTE E'
(SELECT SUM((length/1000))::integer FROM ' || schema || '."' || tablename || '"):: integer' ;
END ;
$function$