Insert_all returning row_number?

Questions / Help
, ,
1

I have query like this that returns records with a virtual field row_number

from e in Enrollment,
  order_by: [desc: e.processed_at],
  select: %{e | row_number: over(row_number(), partition_by: e.class_id, order_by: e.processed_at)}

I am also doing some bulk insert_all. This is how I get the newly inserted records:

Repo.insert_all(Enrollment, enrollments_attrs,
      on_conflict: :nothing,
      conflict_target: [:class_id, :user_id],
      returning: true
    )

I was wondering whether it is somehow possible to get the row_number already during the insertion? I would expect the returning option to resemble the select clause from the above query. But I just can’t figure out what the syntax would be.

To be honest, I’m not sure it’s even possible by looking at Postgres docs.

More simplified example would be a simple insert with returning clause that contains any “calculated” parameter like avg.

Postgres docs for RETURNING is kinda sparse: PostgreSQL: Documentation: 14: 6.4. Returning Data from Modified Rows

I was also looking at postgres WITH that can be combined with UPDATE or INSERT, see PostgreSQL: Documentation: 14: 7.8. WITH Queries (Common Table Expressions)

Do you think it’s even possible? Thank you.

2

The documentation for INSERT specifically calls out that the output_expression passed to RETURNING "can use any column names of the table named by table_name".

My interpretation of that statement is that this isn’t possible - RETURNING doesn’t allow arbitrary SQL.

3

Yes, my interpretation is similar. Although such a feature doesn’t sound inconceivable to me.