0

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.

  • I think the issue maybe Npgsql doesn't like the DO anonymous code as when I look at it as it errors its converted all the parameters to values but not the anonymous code – Jon Sep 17 '14 at 16:32
  • I can't really as I'd need to say in the code is this postgres or mssql, if so call the function etc etc :( – Jon Sep 17 '14 at 16:36
  • Unrelated but: WHERE Id = currval('mytable_id_seq') is enough. No need for a select there. – a_horse_with_no_name Sep 17 '14 at 17:54
  • This question has several solutions to the "insert or update" problem: stackoverflow.com/q/1109061/330315 – a_horse_with_no_name Sep 17 '14 at 17:56
3

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:

  • @Jon: You may be interested in the addendum. – Erwin Brandstetter Sep 24 '14 at 22:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.