postgresql动态查询

Question

我需要用以下函数中的参数替换模式和表名(目前工作完美):

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$

我尝试了以下解决方案(及其众多变体):

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$

，但一直运行到以下错误代码:

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

为什么不工作?'length'列包含浮点值。

Answer 1

您必须使用动态SQL，因为您不能为标识符使用参数。

另外，确保通过使用format而不是连接字符串来避免SQL注入:

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

Answer 2

你可以试试:

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$