postgresql - 返回类型字符 varying(70)[] 与预期类型不匹配

Question

我需要帮助来查看以下查询，我有如下表格:

CREATE TABLE "MasterDivision"
(
    "DivisionID" bigserial NOT NULL,
    "DivisionName_EN" character varying(70) NOT NULL,
    "DivisionName_HI" character varying(70) NOT NULL,
    "DivisionCode" character varying(70) NOT NULL,
    "StateID" bigint NOT NULL,
    "CreatedBy" bigint NOT NULL,
    "UpdatedBy" bigint NOT NULL,
    "CreatedIP" character varying(50) NOT NULL,
    "UpdatedIP" character varying(70) NOT NULL,
    "IsActive" boolean NOT NULL,
    "CreationDate" date NOT NULL,
    "UpdationDate" date NOT NULL,
    CONSTRAINT "PK_MasterDivision_DivisionID" PRIMARY KEY ("DivisionID")
)
WITH (
    OIDS=FALSE
);
ALTER TABLE "MasterDivision"
OWNER TO postgres;
GRANT ALL ON TABLE "MasterDivision" TO public;
GRANT ALL ON TABLE "MasterDivision" TO postgres;

我创建了以下自定义类型:

create type type_selectmasterdivisionwithpagging
as
(
    row bigint,
    DivisionID bigint,
    DivisionName_EN character varying(70),
    DivisionName_HI character varying(70),
    DivisionCode character varying(70),
    StateID bigint,
    StateName_EN character varying(70),
    StateName_HI character varying(70)

);

和下面的函数...

CREATE OR REPLACE FUNCTION selectmasterdivisionwithpagging("PageNumber" integer, "PageSize" integer, "WhereClause" text, "OrderByClause" text)
RETURNS SETOF type_selectmasterdivisionwithpagging AS
$BODY$declare 
    _SQL text;      
Begin   
IF $1 <> -1 and $2 <> -1  then

_SQL:='Select * ' ||
      'from ' ||
      '( ' ||
        'SELECT ROW_NUMBER() OVER ('|| $4 ||' ) AS Row' ||
            ',MD."DivisionID" ' ||
            ',MD."DivisionName_EN" '||
            ',MD."DivisionName_HI" '||
            ',MD."DivisionCode" '||
            ',MD."StateID" '||
            ',MS."StateName_EN" '||
            ',MS."StateName_HI" '||
        'from "MasterDivision" as MD ' ||
        'left join "MasterState" as MS ' ||
        'on MD."StateID"=MS."StateID" ' ||
        $3 ||

      ' ) as t' ||
      ' where 1=1 and t.Row between ' || ((($1-1)*$2)+1)||' and ' || ($1*$2) ;

    RETURN query execute _SQL;              
else        
    RETURN query execute    'SELECT ROW_NUMBER() OVER ('|| $4 ||' ) AS Row' ||
                    ',MD."DivisionID" ' ||
                    ',MD."DivisionName_EN" '||
                    ',MD."DivisionName_HI" '||
                    ',MD."DivisionCode" '||
                    ',MD."StateID" '||
                    ',MS."StateName_EN" '||
                    ',MS."StateName_HI" '||
                'from "MasterDivision" as MD ' ||
                'left join "MasterState" as MS ' ||
                'on MD."StateID"=MS."StateID" ';
end IF;
end;$BODY$
LANGUAGE plpgsql IMMUTABLE
COST 100
ROWS 1000;
ALTER FUNCTION selectmasterdivisionwithpagging(integer, integer, text, text)
OWNER TO postgres;

以上所有查询都执行得很好，但是当我使用 select selectmasterdivisionwithpagging(1,2,' where 1=1 ','Order by MS."StateName_EN"') 时出现错误:

错误:查询结构与函数结果类型不匹配
详细信息:返回的类型字符 varying(70)[] 与第 7 列中预期的类型字符 varying(70) 不匹配。

如果需要更多信息，请告诉我。谢谢大家

Answer 1

1. 请在不需要时停止使用EXECUTE。如果这样做，请使用参数化形式。您当前的函数容易受到各种函数内 sql 注入(inject)的攻击。<​​/p>

2. 停止编写依赖 SQL 注入(inject)漏洞工作的函数。如果我在你的 where 子句中输入 '1=1;从“MasterDivision”中删除； --' 然后你会得到一个非常令人讨厌的惊喜。

3. 您的错误表明 "MasterState"."StateName_EN" 不是您认为的类型。检查那里。在我看来，您可能将其作为 varchar(70)[] 而不是 varchar 字段。