postgresql dynamic query

Question

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.

Laurenz Albe · Accepted Answer · 2021-10-27 07:15:01Z

1

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';

edited Oct 27, 2021 at 7:15

answered Oct 25, 2021 at 19:28

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

EricBHK Over a year ago

script is running on my local machine, no SQL injection danger

Laurenz Albe Over a year ago

That doesn't necessarily help. You may have a table with a non-standard name...

EricBHK Over a year ago

okay, but that is not really the probelm right now. My issue remains that my original function without parameters (see code above) works fine, but that - as soon as I use the two paramaters -- I get all kinds of errors that seem not to be related to the paramaters itself.

Laurenz Albe Over a year ago

Yes. And that is what my answer solves.

EricBHK Over a year ago

unfortunatly, your solution throws a parenthesis mismatch. after orrecting, it throws follwing error : .... psycopg2.errors.UndefinedObject: type "format" does not exist LINE 1: SELECT format('(SELECT SUM((length/1000))::integer FROM %I.%... ^ QUERY: SELECT format('(SELECT SUM((length/1000))::integer FROM %I.%I', schema, table_name) 'INTO _check' CONTEXT: PL/pgSQL function my_function_119(text,text) line 4 at EXECUTE

|

Edouard · Accepted Answer · 2021-10-26 11:32:13Z

0

You can try this :

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

edited Oct 26, 2021 at 11:32

answered Oct 25, 2021 at 19:33

Edouard

7,1391 gold badge11 silver badges24 bronze badges

7 Comments

EricBHK Over a year ago

non of the two solutions above fix this issue. they just produce other error messages

Edouard Over a year ago

What is the data type of the column 'length' in the table public.National_Grid_multiline ?

Edouard Over a year ago

What is the result when you execute : SELECT SUM(length/1000) FROM public.National_Grid_multiline ?

EricBHK Over a year ago

data type of the column in database is double precision (but replacing that in the function by integer does not solve the issue neither. The answer posted by you throws following error : -> syntax error at or near "END" SELECT SUM(length/1000) FROM public.National_Grid_multiline -> relation "public.national_grid_multiline" does not exist SELECT SUM(length/1000) FROM public."National_Grid_multiline" -> 684921.2474125557 SELECT SUM("length"/1000) FROM public."National_Grid_multiline" 684921.2474125557

Edouard Over a year ago

I have updated the function my_function in my previous answer to make it working. The error "relation "public.national_grid_multiline" does not exist" means the table public.national_grid_multiline doesn't exist in the database you are connected to, so you have to solve this issue before going further.

|

Collectives™ on Stack Overflow

postgresql dynamic query

2 Answers 2

7 Comments

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related