Re: pgsql code is not working.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]


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;


$$ 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.

[Index of Archives]     [Postgresql Home]     [Postgresql General]     [Postgresql Performance]     [Postgresql PHP]     [Postgresql Jobs]     [PHP Users]     [PHP Databases]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Yosemite Forum]

  Powered by Linux