Thread: pgsql code is not working.
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
|
|
Attachment
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.
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.
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?