Returning record ID's from PostgreSQL using PetaPoco

Question

PetaPoco PostgreSQL 11.1

I am attempting to get a list of deleted record id's. This does NOT work:

var sql = new Sql()
      .Append(@"; WITH _in (lastname, firstname, birthdate, description, dencounter) AS (
                  VALUES ( UPPER(@0), UPPER(@1), @2::date, LOWER(@3), @4::date  )
                       )
                   DELETE FROM dx d
                    USING _in n
                   JOIN patients pt ON (pt.lastname, pt.firstname, pt.birthdate) = (n.lastname, n.firstname, n.birthdate)
                  JOIN disease z ON (z.description = n.description)
                  WHERE (d.patient_recid, d.disease_recid, d.dposted)=(pt.recid, z.recid, n.dencounter)
               RETURNING d.recid;", lastName, firstName, birthDate, description, tencounter);

      return db.Fetch<int?>(sql);

The RETURNING is not being honored. (I receive the "1" showing Delete success not the recid value). It runs correctly in pgAdmin 4.

Is there anyway to do this with PetaPoco (and C#) ? I am looking not just for a single recid, but an IEnumerable of int? from many deletions.

TIA

Answer 1

Took me a little while to understand what was happening here.

The Fetch<T> runs a query and returns the result set as a typed list. Straight from the docs.

You will likely need to honour the typed part of this.

Hopefully, the following demonstrates what is happening.

public class TypedReturn { 
   public string recid { get; set; }
}

var sql = new Sql()
    .Append(@"; WITH _in (lastname, firstname, birthdate, description, dencounter) AS (
                VALUES ( UPPER(@0), UPPER(@1), @2::date, LOWER(@3), @4::date  )
                )
                  DELETE FROM dx d
                    USING _in n
                   JOIN patients pt ON (pt.lastname, pt.firstname, pt.birthdate) = (n.lastname, n.firstname, n.birthdate)
                  JOIN disease z ON (z.description = n.description)
                  WHERE (d.patient_recid, d.disease_recid, d.dposted)=(pt.recid, z.recid, n.dencounter)
               RETURNING d.recid;", lastName, firstName, birthDate, description, tencounter);

List<TypedReturn> returnValues = db.Fetch<TypedReturn>(sql);

foreach(var returnValue in returnValues) 
{
    Console.WriteLine(returnValue.recid);
}

Or you could use dynamic, which you would do like so

var sql = new Sql()
    .Append(@"; WITH _in (lastname, firstname, birthdate, description, dencounter) AS (
                VALUES ( UPPER(@0), UPPER(@1), @2::date, LOWER(@3), @4::date  )
                )
                  DELETE FROM dx d
                    USING _in n
                   JOIN patients pt ON (pt.lastname, pt.firstname, pt.birthdate) = (n.lastname, n.firstname, n.birthdate)
                  JOIN disease z ON (z.description = n.description)
                  WHERE (d.patient_recid, d.disease_recid, d.dposted)=(pt.recid, z.recid, n.dencounter)
               RETURNING d.recid;", lastName, firstName, birthDate, description, tencounter);

List<dynamic> returnValues = db.Fetch<dynamic>(sql);

foreach(var returnValue in returnValues) 
{
    Console.WriteLine(returnValue.recid);
}