PostgreSQL Function Invocation Error: How to Fix 'function ... does not exist' due to Type Mismatch?

ERROR: Function Invocation Mismatch and Argument Type Casting

In your code, you encounter the error: "ERROR: function ... does not exist and HINT: No function matches the given name and argument types." This error arises from a mismatch between the function definition and the arguments passed during invocation.

Specifically, your function, FnUpdateSalegtab09, has multiple parameters declared as 'smallint' data type. However, when you call the function, you pass numeric literals (e.g., '12') as arguments for these parameters.

Type Casting Issue

PostgreSQL assumes numeric literals without decimal points or exponents as type 'integer' by default. However, the parameters in your function are defined as 'smallint,' which is a range of -32768 to 32767. Passing an integer value without explicit casting beyond this range can lead to data truncation or errors.

Solution

To resolve this issue, you have two options:

1. Explicit Type Casting in Function Invocation:

You can explicitly cast the numeric literals to 'smallint' in the function invocation using the '::smallint' syntax. For example:

select FnUpdateSalegtab09 (4, 1, 0, 12, 1::smallint, '9'::varchar,....

2. Pass Untyped Literals:

Alternatively, you can use untyped string literals in the function call. PostgreSQL will then infer the correct data type based on the function parameters. To do this, enclose the numeric literal in single quotes:

select FnUpdateSalegtab09 (4, 1, 0, 12, '1':: smallint,....

Example

Consider the following corrected function invocation:

select FnUpdateSalegtab09 (4, 1, 0, 12::smallint, 1, '9'::varchar,....

This invocation explicitly casts the integer literal '12' to 'smallint,' ensuring that the function parameters match the expected data types.

The above is the detailed content of PostgreSQL Function Invocation Error: How to Fix 'function ... does not exist' due to Type Mismatch?. For more information, please follow other related articles on the PHP Chinese website!