3

I'm creating a mini DB handling class and for convinience purposes, I want my generated INSERT queries are always try to return last insert id or null, if not applicable. I had tried to use:
INSERT INTO ... RETURNING lastval();
it works fine with tables which have a sequence, but if table have no sequence key field, that insert nothing and falls with error: lastval is not yet defined in this session.

How to check in RERURNING block, is sequence generator was used in this session or silently return last id or null, without error raising?

Note: for usablity purposes, I want to avoid direct specifing of PK field name.

Vasiliy
  • 31
  • 1
  • 3
  • 1
    Specifying the column directly in the `returning` clause is the only solution. –  Oct 07 '15 at 11:57
  • 2
    `INSERT INTO ... RETURNING column_name;` – Vivek S. Oct 07 '15 at 11:57
  • There was discussion of supporting something like a `RETURNING GENERATED KEYS` or `RETURNING PRIMARY KEY` feature, but it got stuck in implementation detail, and turned out to be way more complicated than it first looked. So at this point, yeah, you've just got to use the column name, or `*`. You can look up the key columns from the `information_schema` and dynamically modify your SQL if you need to. – Craig Ringer Oct 07 '15 at 12:11
  • See [How to find out if a sequence was initialized in this session?](http://stackoverflow.com/questions/24182521/how-to-find-out-if-a-sequence-was-initialized-in-this-session/24186770#24186770) – klin Oct 07 '15 at 13:33
  • Solution with user stored function is good, the only minor disadvatage, it requires preinitialization. Is there a way to run such function inline? `DO` seems not working here. Solution with `*` also looks interesting, assuming that PK is always first field, it can be relayed as insert id in programm. Is there a way to select only first field from `*` without accessing `information_schema`? – Vasiliy Oct 08 '15 at 12:54

0 Answers0