CODEWITHSUNDEEP
postgresql
truvaking
truvaking

Reputation: 51

Return the number of rows with a where condition after an INSERT INTO? postgresql

I have a table that regroups some users and which event (as in IRL event) they've joined.

I have set up a server query that lets a user join an event.

It goes like this :

INSERT INTO participations
VALUES(:usr,:event_id)

I want that statement to also return the number of people who have joined the same event as the user. How do I proceed? If possible in one SQL statement.

Thanks

Upvotes: 1

Views: 109

Answers (2)

Raymond Nijland
Raymond Nijland

Reputation: 11602

You can use a common table expression like this to execute it as one query.

with insert_tbl_statement as  (

    insert into tbl values (4, 1) returning event_id
)
select (count(*) + 1) as event_count from tbl where event_id = (select event_id from insert_tbl_statement);

see demo http://rextester.com/BUF16406

Upvotes: 1

McNets
McNets

Reputation: 10807

You can use a function, I've set up next example, but keep in mind you must add 1 to the final count because still transaction hasn't been committed.

create table tbl(id int, event_id int);

✓

insert into tbl values (1, 2),(2, 2),(3, 3);

3 rows affected

create function new_tbl(id int, event_id int)
returns bigint as $$

    insert into tbl values ($1, $2);

    select count(*) + 1 from tbl where event_id = $2;

$$ language sql;

✓

select new_tbl(4, 2);

| new_tbl |
| ------: |
|       4 |

db<>fiddle here

Upvotes: 0

Related Questions