Обсуждение: pgsql code is not working.

Поиск
Список
Период
Сортировка

pgsql code is not working.

От
"Singh, Gambhir"
Дата:

Hi All,

 

I was trying to execute the below mentioned code but to not able to make it workable.

 

Table Name – additional_details

Sequence Name – additional_details_id_seq

 

 

DO $$

DECLARE PKEY VARCHAR;

DECLARE MAX_VAL INTEGER;

BEGIN

 

SELECT pg_attribute.attname INTO PKEY

FROM pg_index, pg_class, pg_attribute

WHERE pg_class.oid = ‘additional_details’::regclass

AND indrelid = pg_class.oid

AND pg_attribute.attrelid = pg_class.oid

AND pg_attribute.attnum = any(pg_index.indkey)

AND indisprimary;

 

SELECT MAX(pkey) into MAX_VAL FROM additional_details;

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.

 

ERROR:  operator does not exist: character varying + integer

LINE 1: SELECT setval('additional_details_id_seq',MAX_VAL + 1)

                                                          ^

HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

QUERY:  SELECT setval('additional_details_id_seq',MAX_VAL + 1)

CONTEXT:  PL/pgSQL function inline_code_block line 15 at SQL statement

SQL state: 42883

 

 

To know the values stored in variable I commented the “SELECT setval” line, after that I got this result.

 

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.

 

Please help me to solve this issue.

 

Thanks & Regards

Gambhir Singh

____
Gambhir Singh
Contractors ‑ Projects
Kemper Corporate Services
Chicago, IL 60601
E gsingh2@kemper.com
kemper.com
CONFIDENTIALITY NOTICE: This communication may contain ​
confidential information intended only for the addressee(s). If you received 
this communication in error, please notify the sender and delete it from your 
system.
​ 
Вложения

Re: pgsql code is not working.

От
"David G. Johnston"
Дата:
On Fri, May 12, 2023 at 6:07 PM Singh, Gambhir <gsingh2@kemper.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.

Re: pgsql code is not working.

От
"David G. Johnston"
Дата:


On Friday, May 12, 2023, David G. Johnston <david.g.johnston@gmail.com> wrote:
On Fri, May 12, 2023 at 6:07 PM Singh, Gambhir <gsingh2@kemper.com> wrote: 

 

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?

Never mind that, the error in question is happening before the setval call where your quoting typo exists in the example code.  Changing to varchar and raise notice works, though the original error message provided the exact same information.

David J.