具有定义类型的调用函数 [英] Call function with defined types

问题描述

我创建了自己的Postgres函数:

I created my own Postgres function:

CREATE OR REPLACE FUNCTION myfunction(
param_val1 varchar(255),
param_cal2 VARCHAR(255), 
param_val3 VARCHAR(255),
param_val4 current_category,
param_val5 current_type,
param_val6  VARCHAR(255),
param_val7 bigint,
param_val8 text )

我想用jdbc来调用它:

I want to call it with jdbc:

callableStatement = conn.prepareCall("{call myfunction(?,?,?,?,?,?,?,?)}");

  callableStatement.setString(1, "item1");
  callableStatement.setString(2, "domain1");
  callableStatement.setString(3, "indicator1");
  callableStatement.setString(4, "PROPERTY");
  callableStatement.setString(5, "STRING");
  callableStatement.setString(6, "on");
  callableStatement.setLong(7, 1396983600);
  callableStatement.setString(8, "testing");

但是我得到:

org.postgresql.util.PSQLException: ERROR: function addindicators(character varying,     character varying, character varying, character varying, character varying, character varying, bigint, character varying) does not exist
  Hint: No function matches the given name and argument types. You might need to add     explicit type casts.
  Position: 15

current_category 和 current_type 是枚举.我需要创建Java枚举然后使用setObject(#，Object)吗?

current_category and current_type are enums. Do I need to create java enums and then use setObject(#, Object)?

推荐答案

一种方法是将函数定义为采用 text / varchar 参数并将其强制转换为<函数内部的code> enum 类型.示例代码:

One way would be to define the function as taking text / varchar parameters and cast to your enum types inside the function. Example Code:

CREATE OR REPLACE FUNCTION myfunction( ..., param_val4 text, ...)
...
DECLARE
  _param_val4 current_category := param_val4::current_category;
  ...
BEGIN
...

但是Java中可能有一种方法可以在没有显式类型的情况下调用此函数(我不知道).如果您可以为枚举发送无类型的字符串(类型为"unknown" 的字符串文字)，则Postgres会自动找到该函数的最佳匹配.

But there is probably a way in Java to call this function without explicit types (I don't know of). If you can send untyped string, (string literals of type "unknown") for the enums, Postgres finds the best match for the function automatically.

这篇关于具有定义类型的调用函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！