All we need is an easy explanation of the problem, so here it is.
I am using
Npgsql and I want to be able to do an
if statement that will do an
update and then return a value so when
ExecuteScalar is called it reads that value. This was done fine in MSSQL but I’m now trying to support PostgreSQL and I can’t seem to get it right.
The SQL is similar to this:
insert into mytable (name, uniquecallid) values (@name, @uniquecallid); DO $do$ BEGIN IF coalesce(@UniqueCallId,'') = '' THEN SET @UniqueCallId = coalesce(@CallIdPad, '') || cast(varchar(50), select currval('mytable_id_seq')); UPDATE mytable SET UniqueCallId = @UniqueCallId WHERE Id = select currval('mytable_id_seq'); END IF; END $do$ select currval('mytable_id_seq');
This errors with a “syntax error near select”.
What can I do to get this to return the latest value inserted?
I believe this is a Npgsql issue as when inspecting the SQL that it executes the parameters are replaced with the values but the
DO block doesn’t have the values in place of parameters.
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
Immediate causes for the error:
WHERE id =
And a missing semicolon at the end of the
On a closer look, this does not seem to be another case of
It would seem one of the inserted columns needs to depend on multiple input values in combination with the freshly retrieved value for a serial ID. Try this largely simplified statement:
INSERT INTO mytable (id, name, uniquecallid) SELECT t.id , t.name , CASE WHEN t.uniquecallid <> '' THEN t.uniquecallid ELSE COALESCE(t.callidpad, '') || t.id END FROM ( SELECT nextval('mytable_id_seq') AS id , '@name'::text AS name -- provide values here, once , '@UniqueCallId'::text AS uniquecallid , '@CallIdPad'::text AS callidpad ) t RETURNING id;
An explicit cast to
text (or your actual undisclosed data type(s)) is needed.
This is equivalent to the presented code, just with legal syntax and much faster – unless there are triggers or non-standard default values you did not declare …
On an even closer look, this can be simplified further:
INSERT INTO mytable (name, uniquecallid) SELECT '@name'::text , COALESACE( NULLIF('@UniqueCallId'::text, '') , COALESCE('@CallIdPad'::text, '') || currval('mytable_id_seq')) RETURNING id;
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂