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.