RETURN value directly from INSERT with RETURNING clause

Question

I have a function that ends with:

    INSERT INTO configuration_dates (
      cols...
    ) VALUES (
      values...
    ) RETURNING id INTO ret_id;
    RETURN ret_id;

And I would like to remove the INTO ret_id part, and instead do something like:

RETURN (INSERT INTO configuration_dates (
  weekly_date_configuration_id,
  "from",
  "to",
  price,
  activity_configuration_id
) VALUES (
  wdc_id,
  from_ts,
  from_ts + wdc.duration,
  wdc.price,
  wdc.activity_configuration_id
) RETURNING id);

But I haven't found how to achieve this.

EDIT: adding entire function

CREATE FUNCTION make_date_from_configuration(wdc_id UUID, from_date DATE)
  RETURNS INTEGER AS $$
DECLARE
  from_dow INT := EXTRACT(isodow FROM from_date); -- day of the week (1 - 7)
  wdc weekly_date_configurations;
  from_ts TIMESTAMP;
  ret_id INTEGER;
BEGIN
  SELECT * INTO wdc
  FROM weekly_date_configurations wdc
  WHERE id = wdc_id;

  IF FOUND
    AND wdc.valid_through @> from_date             -- starts in the date range
    AND get_bit(wdc.weekdays, from_dow - 1) = 1    -- valid day of the week
  THEN
    from_ts := from_date || ' ' || wdc.start_time;

    INSERT INTO configuration_dates (
      weekly_date_configuration_id,
      "from",
      "to",
      price,
      activity_configuration_id
    ) VALUES (
      wdc_id,
      from_ts,
      from_ts + wdc.duration,
      wdc.price,
      wdc.activity_configuration_id
    ) RETURNING id INTO ret_id;
    RETURN ret_id;
  ELSE
    RETURN NULL;
  END IF;
END;
$$ LANGUAGE plpgsql;

Answer 1

You can simplify the whole function and use an OUT parameter, so the value is returned at the end of the function automatically:

CREATE OR REPLACE FUNCTION make_date_from_configuration(
          wdc_id uuid
        , from_date date
        , OUT ret_id int) AS
$func$
BEGIN
   INSERT INTO configuration_dates (
          weekly_date_configuration_id, "from", "to", price, activity_configuration_id)
   SELECT wdc_id
       , (from_date || ' ' || wdc.start_time)::timestamptz
       , (from_date || ' ' || wdc.start_time)::timestamptz + wdc.duration
       , wdc.price, wdc.activity_configuration_id
   FROM  weekly_date_configurations wdc
   WHERE wdc.id = wdc_id
   AND   wdc.valid_through @> from_date                        -- starts in the date range
   AND   get_bit(wdc.weekdays
               , EXTRACT(isodow FROM from_date)::int - 1) = 1  -- valid day of the week
   RETURNING id
   INTO  ret_id;  -- returned at the end automatically
END
$func$  LANGUAGE plpgsql;

All the variables and assignments and conditional expressions are just needless noise. Can be a single SQL command: shorter and faster.

You still need the INTO clause, though. (And I fail to see why you try to avoid that.)

Or you can use a plain SQL function instead, to return the value directly:

CREATE OR REPLACE FUNCTION make_date_from_configuration(wdc_id uuid, from_date date)
  RETURNS int AS
$func$
   INSERT INTO configuration_dates (
               weekly_date_configuration_id, "from", "to", price, activity_configuration_id )
   SELECT wdc_id
       , (from_date || ' ' || wdc.start_time)::timestamptz
       , (from_date || ' ' || wdc.start_time)::timestamptz + wdc.duration
       , wdc.price, wdc.activity_configuration_id
   FROM  weekly_date_configurations wdc
   WHERE wdc.id = wdc_id
   AND   wdc.valid_through @> from_date                      -- starts in the date range
   AND   get_bit(wdc.weekdays
               , EXTRACT(isodow FROM from_date)::int- 1) = 1 -- valid day of the week
   RETURNING id  -- return directly
$func$  LANGUAGE sql;

Answer 2

Use RETURN QUERY

RETURN QUERY ...

Or if you're returning a single row, not in a RETURNS TABLE or RETURNS SETOF ... function, I think you can store the result into a record-valued variable and return that. Untested:

DECLARE
    my_result record;
BEGIN
    INSERT INTO ...
    RETURNING ...
    INTO my_result;

    RETURN my_result;
END;