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;
Best Answer
You can simplify the whole function and use an
OUT
parameter, so the value is returned at the end of the function automatically: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: