postgresql 查询结构与plpgsql函数中的函数结果类型不匹配

wxclj1h5 于 2023-02-22 发布在 PostgreSQL

关注(0)|答案(1)|浏览(239)

CREATE 
OR REPLACE FUNCTION public.sp_production_snapshot(periodid integer, flagdc integer) RETURNS TABLE(
  prod_period_id integer, 
  period character varying, 
  nosp character varying, 
  policy_no character varying, 
  policy_name character varying, 
  status_id integer, 
  pol_status character varying, 
  family_bisnis character varying, 
  tahun_ke integer, 
  year_comm integer, 
  payment integer, 
  plan_code character varying, 
  life_premium double precision, 
  rider_premium double precision, 
  extra_premium double precision, 
  total_premium double precision, 
  mop integer, 
  ccy_code character varying, 
  ccy_rate double precision, 
  post_date date, 
  commence_date date, 
  issue_date date, 
  stream_start_date date, 
  due_date date, 
  app_code character varying, 
  policy_cat character varying, 
  apcrd_a_amt double precision, 
  apcrd_b_amt double precision, 
  apcrd_d_amt double precision, 
  fyip_credit_amt double precision, 
  comm_npk_id integer, 
  comm_npk integer, 
  comm_npk_name character varying, 
  comm_rank_id integer, 
  comm_rank_code integer, 
  comm_rank_name character varying, 

) LANGUAGE plpgsql AS $function$ BEGIN RETURN QUERY . . . . . . . . .

;

当我这样运行时：

select * from sp_production_snapshot(865, 1);

我得到错误：

SQL Error [42804]: ERROR: structure of query does not match function result type
  Detail: Returned type bigint does not match expected type integer in column 1.
  Where: PL/pgSQL function sp_production_snapshot(integer,integer) line 3 at RETURN QUERY

我的脚本出了什么问题？我刚刚从gorm v1迁移到gorm v2。之前一切都很好，但是在迁移到gorm v2之后很多东西都坏了。

postgresql

来源：https://stackoverflow.com/questions/75446814/structure-of-query-does-not-match-function-result-type-in-plpgsql-function

1条答案

按热度按时间

abithluo1#

函数体中RETURN QUERY之后的语句可能选择了定义为bigint/bigserial类型的标识符。假设此bigint值将Map到输出integer并不总是安全的，因为bigint可以容纳比integer更大的值。请参阅文档：

integer可以是-2147483648到+2147483647之间的任意值
bigint可以从-9223372036854775808到+9223372036854775807
bigserial可以从+1变为+9223372036854775807

如果你偶然发现prod_period_id的值为214748364***8***，你会得到一个ERROR: integer out of range。要解决这个问题，你需要相应地改变函数的输出类型：

DROP FUNCTION public.sp_production_snapshot(integer,integer);
CREATE FUNCTION public.sp_production_snapshot(
  periodid integer, --should be bigint too if it corresponds to prod_period_id
  flagdc integer) 
RETURNS TABLE(
  prod_period_id bigint,--change from integer to bigint
  period character varying, 
...

根据RETURN QUERY的结构及其源代码，它可能不是唯一需要更改为bigint的integer输出值。注意，您需要进一步更改DROP，然后重新-CREATE该函数，以避免在尝试CREATE OR REPLACE时抛出ERROR: cannot change return type of existing function。
如果输出表遵循其它表的结构，则可以使用LIKE语法，而不是将其复制到函数输出表定义中：

DROP FUNCTION public.sp_production_snapshot(integer,integer);
CREATE FUNCTION public.sp_production_snapshot(
  periodid integer, 
  flagdc integer) 
RETURNS TABLE (LIKE your_table_schema.your_table_name) as 
$function$
  ... 
$function$ language plpgsql;

或RETURNS SETOF：

DROP FUNCTION public.sp_production_snapshot(integer,integer);
CREATE FUNCTION public.sp_production_snapshot(
  periodid integer, 
  flagdc integer) 
RETURNS SETOF your_table_schema.your_table_name language plpgsql as 
$function$
  ... 
$function$;

另外，如果你所做的只是返回一个查询，而不使用PL/pgSQL变量、循环、异常处理，你可以坚持使用普通的LANGUAGE SQL，因为inlining可以获得更好的性能;如果它所做的只是在其他地方查找东西，而不修改任何东西，那么把它也变成STABLE和PARALLEL SAFE以实现更好的优化和执行计划可能是值得的。

DROP FUNCTION public.sp_production_snapshot(integer,integer);
CREATE FUNCTION public.sp_production_snapshot(
  periodid integer, 
  flagdc integer) 
RETURNS SETOF your_table_schema.your_table_name 
STABLE PARALLEL SAFE LANGUAGE SQL AS 
$function$
  SELECT * FROM your_table_schema.your_table_name AS a
  WHERE a.prod_period_id = $1 AND a.flagdc = $2;
$function$;

赞(0）回复(0）举报 2023-02-22

我来回答

postgresql 查询结构与plpgsql函数中的函数结果类型不匹配

1条答案

相关问题

热门标签

最新问答