Re: pgsql code is not working.

On Fri, May 12, 2023 at 6:07 PM Singh, Gambhir <gsingh2(at)kemper(dot)com> 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.

https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

David J.