Subselect in insert without additional API call

[maxhayward]

Environment

• PostgreSQL version: postgres:14 Docker image
• PostgREST version: postgrest/postgrest:v9.0.0 Docker image
• Operating system: MacOS 12.2.1

Description of issue

For the following example tables:

create table recipe (
	recipe_id serial primary key,
	recipe_name varchar(255) unique not null
);

create table ingredient (
	ingredient_id serial primary key,
	ingredient_name varchar(255) unique
);

create table recipe_ingredient (
	recipe_id int references recipe(recipe_id) not null,
	ingredient_id int references ingredient(ingredient_id) not null,
	record_status int not null default 0,
	primary key (recipe_id, ingredient_id)
);

I would like to execute the following as a single call to Postgrest:

insert into recipe_ingredient (recipe_id, ingredient_id) values ((select recipe_id from recipe where recipe_name = 'Lasagne'), (select ingredient_id from ingredient where ingredient_name = 'Cheese'));

Is this possible?

[steve-chavez]

insert into recipe_ingredient (recipe_id, ingredient_id) values ((select recipe_id from recipe where recipe_name = 'Lasagne'), (select ingredient_id from ingredient where ingredient_name = 'Cheese'));
Is this possible?

Not exactly like that, but in #818 we discussed doing something like:

POST /recipe?columns=id,ingredient(id)
[
  {"id": 1, "ingredient": [{"id": 13}]}
]

That would detect the m2m relationship and automatically create the junction table(recipe_ingredient) row.

[maxhayward]

Thanks the for the response. I'm trying to map this solution to what I'm doing.

• Won't this insert into the recipe table? In this example I only want to insert into recipe_ingredient.
• The POST data here implies I already know the IDs. I would like to only pass the recipe_name and ingredient_name in the data, and have Postgrest infer the IDs before doing the insert.

Hope that makes sense.

[wolfgangwalther]

There are a couple of different ways to do this:

• For one, you can just remove the surrogate PK and use the unique names as PKs - and then references them in the mapping table. This will allow you to run the insert on that table directly.
• Alternatively, you could expose the mapping table as a view - in which you expose those names (instead of or in addition to the IDs). You can then create an insert trigger on that view, which will fetch the IDs when an insert is made with names, but without IDs.
• You can just create a custom RPC that does the insert.