1

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;
5

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;
4

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;
  • Now I get cannot use RETURN QUERY in a non-SETOF function – chamini2 Sep 6 '16 at 6:22
  • 3
    @chamini2 Well, it would've helped if you'd shown your function definition... – Craig Ringer Sep 6 '16 at 6:55
  • Added the function, @Craig Ringer – chamini2 Sep 7 '16 at 23:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.