Use serial ID for multiple columns and return it from INSERT

Question

I am using Npgsql and I want to be able to do an insert, and 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.

Answer 1

Syntax errors

Immediate causes for the error:

WHERE  id = select currval('mytable_id_seq');

And a missing semicolon at the end of the DO statement:

...
$do$;

Better query

On a closer look, this does not seem to be another case of UPSERT. 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 ...

Simpler with currval()

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;

Detailed explanation:

• PostgreSQL next value of the sequences?