1

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.

Andrey Vladimirovich
  • 17
  • 5
  • You can cast `text` to `cid`. See if `(1::text)::cid` works. – Mike Organek Jul 11 '22 at 15:04
  • @MikeOrganek I try it. SELECT "sa_db_test.textTest"((1::text)::cid); but i have the same error: ERROR: function sa_db_test.textTest(cid) does not exist. No function matches the given name and argument types. You might need to add explicit type casts. – Andrey Vladimirovich Jul 11 '22 at 15:16
  • @MikeOrganek Thank you so mutch. Your approach is work correct. I do not know why, but it worked only after creating a couple of new functions, apparently I have problems with the backend. – Andrey Vladimirovich Jul 11 '22 at 15:44

2 Answers2

1

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.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • Thank you. It's work. In jdbc, apparently, the best way would be to add the query itself with the transformation like conn.prepareCall("{ call test4(?::cid) }") or is it possible to do this using methods built into the api of the jdbc library ? – Andrey Vladimirovich Jul 11 '22 at 15:52
0

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

Andrey Vladimirovich
  • 17
  • 5