How can I port this mongo query to postgres?

Question

I have an atomic update that finds a record by the lowest value in a property, increments that value, and returns the result in one query.

If I were in SQL Server I would use DECLARE and SET inside of a transaction and do a multiple queries, but this postgres DO syntax is pancake to me.

I see DO $$DECLARE r record; which is a good start, but can I SET or by other means assign a single record to that variable?

In Mongo:

this.findOneAndUpdate(
    { active: true },
    { $inc: { userCount: 1 } },
    { sort: { userCount: 1 } }, // grab the lowest count for update
    cb
);

In SQL Server I would do something like this: (man it's been a while...)

declare @id int
begin
    set @id = select top 1 id 
        from foos 
        where active = 1 
        order by userCount desc

    update foos 
        set userCount = userCount + 1 
        where id = @id

    select foos 
        where id = @id
end

Denis de Bernardy · Accepted Answer · 2013-12-07 14:33:57Z

3

No need for a do statement... Use a normal update statement with a returning clause, e.g.:

update foos 
set userCount = userCount + 1 
where id = (
      select id 
      from foos 
      where active = 1 
      order by userCount desc
      limit 1
    )
returning *

answered Dec 7, 2013 at 14:33

Denis de Bernardy

79.1k14 gold badges138 silver badges158 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How can I port this mongo query to postgres?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related