I need to call the function which has cid ass attribute from jdbc. The documentation says about type cid - "Command identifiers are also 32-bit quantities." I create simple function with cid

CREATE OR REPLACE FUNCTION simplecid(in_param cid)
RETURNS VOID
LANGUAGE plpgsql
AS $function$
BEGIN
 RAISE NOTICE 'Test cidtype';
END;
$function$;

I'm trying to call this from the console. SELECT "simplecid"(123); And i get this error:

: ERROR: function simplecid(integer) does not exist No function matches the given name and argument types. You might need to add explicit type casts.

I'm trying to do an explicit cast: SELECT "sa_db_test.simplecid"(cast(1 as cid));

SQL Error [42846]: ERROR: cannot cast type integer to cid

I'm try to use another type. SELECT "simplecid"(cast('a' as cid));

SQL Error [42883]: ERROR: function simplecid(cid) does not exist No function matches the given name and argument types. You might need to add explicit type casts.

Also i tried call it from jdbc and i have the same error:

CallableStatement callableStatement = conn.prepareCall("{ call sa_db_test.simplecid(?) }");
    callableStatement.setLong(1, 34L);
    callableStatement.execute();

I get this:

Exception in thread "main" org.postgresql.util.PSQLException: ERROR: function sa_db_test.simplecid(bigint) does not exist No function matches the given name and argument types. You might need to add explicit type casts.

CodePudding user response：

String literals (type unknown) can be converted to any data type. So you have three ways to write a constant of type cid:

• CID '123'

• '123'::cid or CAST ('123' AS cid)

• cid('123')

See the documentation for details.

CodePudding user response：

I tried a couple of cases to call it from jdbc. And i founded two:

1. This case is simple. The query is put explicit type casting.

   CallableStatement callableStatement = conn.prepareCall("{ call test4(?::cid) }") callableStatement.setString(1, "123"); ...

2. The second case is more preferably for me, because i can't change query in prepareCall (this is happening elsewhere):

   CallableStatement callableStatement = connection.prepareCall("{ call cid_test(?, ?, ?) }");
    callableStatement.setObject(1, "321", Types.OTHER);
    callableStatement.registerOutParameter(2, (Types.OTHER));
    callableStatement.setObject(3, "123",  Types.OTHER);
    callableStatement.registerOutParameter(3, (Types.OTHER));
    callableStatement.execute();
    System.out.println(((PGobject) callableStatement.getObject(2)).getValue());
    System.out.println(((PGobject) callableStatement.getObject(3)).getValue());
   

It is good to know that a string with letters will not be converted to cid and zero value will be returned in response