On Fri, May 12, 2023 at 6:07 PM Singh, Gambhir <gsingh2@xxxxxxxxxx> wrote:
SELECT MAX(pkey) into MAX_VAL FROM additional_details;
This is not doing what you think it does. It is taking the constant string in the variable "pkey" and finding its maximum value (which, for a constant, is just the value). It is not, as you seem to think, treating the value in "pkey" as column name. It is not possible to directly substitute identifiers into a query using variables. You must instead turn the query into a string with a placeholder (see the format function) and the pass in the variable to the format function for interpolation into the query string as text. Then use EXECUTE to run the query.
SELECT setval(additional_details_id_seq,MAX_VAL + 1);
Raise notice ‘Value pkey: %’,pkey;
Raise notice ‘Value max_val: %’,max_val;
END;
$$ LANGUAGE plpgsql;
ERROR: invalid input syntax for type integer: "additional_details_id"
CONTEXT: PL/pgSQL function inline_code_block line 14 at SQL statement
SQL state: 22P02
But when I change the data type of ‘MAX_VAL’ variable from INTEGER to VRACHAR then got this result.
You get an error about the first argument to your function so you go and change the second one. That doesn't seem like a production debugging choice. You main issue there was the lack of single quotes, which you seem to have later fixed. But given you seem to understand the MAX_VAL is indeed a varchar, and 1 is an integer, the error that there is no addition operator between those shouldn't come as a surprise - how would you add those together?
NOTICE: Value pkey: additional_details_id
NOTICE: Value max_val: additional_details_id
Actually in max_val column, it should display the max value of column in numbers, instead it showing the same value which is stored in pkey variable.
As noted above, that is the expected outcome when you write "max(pkey)" in a query, the variable is interpolated once to a constant value.
David J.
