INSERT in CASE expression from subselect PostgreSQL

I am trying to create a query like this

WITH insert1 as (),
...
subselect1 as (SELECT
   (CASE
     WHEN %s is NOT NULL THEN
          (INSERT INTO Duration (duration)
          VALUES (ROW (%s, %s)) RETURNING id as id_duration)
     ELSE
          (INSERT INTO Distance (length)
          VALUES (ROW (%s, %s)) RETURNING id as id_distance)
       END)),
...
INSERT INTO FinalTable...

I’m having trouble with the syntax, I know. Do you accomplish this by with Insert into?

My plan is: By one WITH statement make several insertions with returning values and finally insert to the FinalTable. Having only INSERT and RETURNING values it works great – I have to refer them in FinalTable e.g. (SELECT id_point from insert3).

But this case – I would like to return value from insert, wrapped in CASE (%s means parametrized query, variables passed from python). So in case first %s is NOT NULL, I have to insert to table Duration, else I have to insert to table Distance.

When inserting in FinalTable, I have references to these tables (columns idDistance, idDuration) – so I would like to write smth like (…, (SELECT id_duration from subselect1), (SELECT id_distance from subselect1)…)

What’s wrong with my syntax?

Answer

It’s very unclear to me what you are trying to achieve, but maybe you are looking for something like this:

WITH insert1 as (
...
),
insert_duration (id_duration) as (
  insert into duration (duration, ....) 
  select .... 
  from (
      values (..),(..)
  ) as t(...)
  where $1 IS NOT NULL --<< first branch of your CASE expression
  returning id
), 
insert_distance (id_distance) as (
  insert into distance (length, ...) 
  select .... 
  from (
      values (..),(..)
  ) as t(...)
  where $1 IS NULL --<< ELSE branch of your CASE expression
  returning id
)
INSERT INTO final_table 
...