2

Here's the scenario:

create table a (
 id serial primary key,
 val text
);

create table b (
 id serial primary key,
 a_id integer references a(id)
);

create rule a_inserted as on insert to a do also insert into b (a_id) values (new.id);

I'm trying to create a record in b referencing to a on insertion to a table. But what I get is that new.id is null, as it's automatically generated from a sequence. I also tried a trigger AFTER insert FOR EACH ROW, but result was the same. Any way to work this out?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Rimantas Jacikevicius
  • 507
  • 6
  • 12
  • It Works fine with me : http://sqlfiddle.com/#!15/bdd7b/1 may be it's related to the version ot your DB. I don't have knowledge on postgresql – Blood-HaZaRd Dec 21 '14 at 10:58
  • @Blood-HaZaRd it works because you have actually inserted id, the actual case is that id is auto generated `DEFAULT next_val('sequence_name')`, and does not get passed into NEW, as the new defines the input values, because rule happens **before** the actual insertion (whitch means that id is not generated yet). Try inserting this into your fiddle `insert into a values ( 'Bouja');` – Rimantas Jacikevicius Dec 21 '14 at 11:24

3 Answers3

6

To keep it simple, you could also just use a data-modifying CTE (and no trigger or rule):

WITH ins_a AS (
   INSERT INTO a(val)
   VALUES ('foo')
   RETURNING a_id
   )
INSERT INTO b(a_id)
SELECT a_id
FROM   ins_a
RETURNING b.*;  -- last line optional if you need the values in return

Related answer with more details:

Or you can work with currval() and lastval():

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
2

Avoid rules, as they'll come back to bite you.

Use an after trigger on table a that runs for each row. It should look something like this (untested):

create function a_ins() returns trigger as $$
begin
  insert into b (a_id) values (new.id);
  return null;
end;
$$ language plpgsql;

create trigger a_ins after insert on a
for each row execute procedure a_ins();
Denis de Bernardy
  • 75,850
  • 13
  • 131
  • 154
  • Read the question! "I also tried it with trigger, that is launched AFTER insert FOR EACH ROW on a table, but result was the same" – Rimantas Jacikevicius Dec 21 '14 at 10:41
  • I did read tthe question, and it's completely unclear within it *why* it didn't work for you, since you didn't provide the code you were using. What I answered is what you need to do in the end. – Denis de Bernardy Dec 21 '14 at 10:48
  • I did **the same** with the trigger. And the result was **the same**. The code would be very obvious, as I provided what attributes were used, and it didn't work because "but what I get is that new.id is null". On the other hand, if your'e sure it must work with a trigger, there might be something I did wrong. So, I'll test it, and provide the code. But this does not change the fact that the answer does not correspond to the question. – Rimantas Jacikevicius Dec 21 '14 at 10:55
  • I edited my answer with what *should* be a working example. (I can't test it, as I've no postgres around.) – Denis de Bernardy Dec 21 '14 at 11:01
  • @DenisdeBernardy here's this site http://sqlfiddle.com/ in case you'll need the postgres around. But it works poorly with triggers. – Rimantas Jacikevicius Dec 21 '14 at 11:05
  • @DenisdeBernardy it worked out, witch means I did something wrong with the trigger. But I've got one last objection to your answer: you shuldn't discourage using rules, they fulfill their role very well, for example when abstracting tables into views and forbidding operations on tables, and they're much simpler. I think that rules should be used on simple cases for separate objects and triggers should be used for more complex cases and be reusable. – Rimantas Jacikevicius Dec 21 '14 at 11:18
  • @RimantasJacikevicius: I really mean no offense, but I don't think you've any idea of what you're talking about. http://stackoverflow.com/questions/26268322/postgres-rules-preventing-cte-queries/27290478#27290478 http://stackoverflow.com/questions/5894142/what-are-postgresql-rules-good-for/5927860#5927860 – Denis de Bernardy Dec 21 '14 at 11:24
  • @DenisdeBernardy having issues with a feature does not means it doesn't have a good use case, same could be told about inheritance: cranky, discouraged to use, but is still great in some cases (pardon if still sound like nonsence) – Rimantas Jacikevicius Dec 21 '14 at 11:42
  • It's not so much of "having issues with a feature" as it is that the feature might get deprecated, and then removed entirely. Some features introduced in recent years (e.g. CTEs) simply do not support rules. You're welcome to ignore the suggestion, of course. But in due time, I'd advise that you stand for rewrites, and that you may run into very hard to diagnose bugs. (See e.g. how this trigger sequentially inserts a row at a time, while the rule version bulk inserts them. It looks petty and unimportant, but it can degenerate into a world of subtle differences.) – Denis de Bernardy Dec 21 '14 at 12:19
-3

Don't use triggers or other database Kung fu. This situation happens every moment somewhere in the world - there is a simple solution:

After the insertion, use the LASTVAL() function, which returns the value of the last sequence that was auto-incremented.

Your code would look like:

insert into a (val) values ('foo');
insert into b (a_id, val) values (lastval(), 'bar');

Easy to read, maintain and understand.

Bohemian
  • 412,405
  • 93
  • 575
  • 722
  • That's not exactly what I was asking, and I actually discourage such patterns. Having such code in database, the first thing that's not easy, that's **maintain**. – Rimantas Jacikevicius Dec 21 '14 at 11:54
  • @RimantasJacikevicius this is what everybody does. This seems to be exactly what you're asking. As for ease, writing triggers is painful and you've just leaked your application into the database. Leaving all code in the application is good design. – Bohemian Dec 21 '14 at 12:10
  • 5
    @Bohemian: `Leaving all code in the application is good design`. That's a completely arbitrary claim. I could answer that putting as much of your logic and rules into the database is better design. The truth is, it all depends on exact requirements. You can have one common DB and ten different applications building on it ... – Erwin Brandstetter Dec 21 '14 at 12:37
  • Actually, it's a myth that putting logic into the database (ie stored procedures) is a good idea. It's a terrible idea. See [this post](http://stackoverflow.com/a/6369030/256196) for just some reasons why. – Bohemian Dec 21 '14 at 12:50
  • 3
    @Bohemian: Putting logic into a database is an *excellent* idea and stored procedures are just *one* possibility there. To what extent (and all the details) depend on actual requirements and skills. Don't oversell your personal opinion as truth. I am not surprised that your favorite tags are Java and MySQL. You won't be surprised my favorite tag is Postgres. – Erwin Brandstetter Dec 21 '14 at 14:44
  • @erwin not wanting to degenerate into a discussion, but which of the points in [this post](http://stackoverflow.com/a/6369030/256196) are untrue? You can write anything in any language, but that doesn't mean you *should*. – Bohemian Dec 21 '14 at 15:41
  • 2
    @Bohemian: Your generalized claims are *false on principal*, because they are in no way connected to actual requirements of a use case. I said it twice I'll say it one final time: it all depends on actual requirements. (Building your case on MySQL is missing the point in particular, but that's just a side issue.) – Erwin Brandstetter Dec 22 '14 at 12:40
  • @erwin yes, I did let that get away from me - I deleted the soapbox part if my comment. And OK: "it depends" (although I can only think of one clear case and that's where there isn't any application code, just the DB, which I could see happening). However, my comments and beliefs expressed on this are not made regarding mysql in particular - they apply to all flavours of DB, and I believe many of the issues I raised would apply to any circumstance. Maybe we could move this to chat and run through my list of negative points and you can tell me which ones you believe are not true. – Bohemian Dec 22 '14 at 14:53
  • @Bohemian: You changed your comment *after* I answered hours later? That's not possible for plain users. While that may have done with best intentions in mind, my comment now looks out of place. I have no interest to continue here. Happy holidays. – Erwin Brandstetter Dec 22 '14 at 18:30