Postgress stored function: RETURN cannot have a parameter in function returning

47 Views Asked by At

I have the following stored function that needs to return a created id as a result of INSERT command. Currently it produces the error: RETURN cannot have a parameter in function returning

DROP FUNCTION IF EXISTS create_bind_interview;
CREATE FUNCTION create_bind_interview(
    VARCHAR(256),
    VARCHAR(256),
    VARCHAR(256),
    VARCHAR(1024),
    VARCHAR(1024),
    BIGINT,
    VARCHAR(64),
    user_roles
)
RETURNS TABLE (interview_id BIGINT) AS $$
DECLARE
    _id BIGINT;
    _interview_free      BOOLEAN;
    _price INT;
    _price_for_interview INT;
    _interview_id BIGINT;
BEGIN
    _id := (SELECT user_id FROM users WHERE email=$1);
    _interview_free := (SELECT free_interview FROM user_settings WHERE user_id = _id);
    _price := (SELECT price FROM user_settings WHERE user_id = _id);
    _price_for_interview := (
        CASE
            WHEN (_interview_free = FALSE) THEN _price
            ELSE 0
        END
    );

    WITH rows AS(
      INSERT INTO interviews (
          interview_price,
          interview_stage,
          interview_date,
          comments,
          topic,
          interview_timezone
      )
      VALUES (
          _price_for_interview,
          $2,
          $3,
          $4,
          $5,
          $7
      )
    RETURNING interviews.interview_id
    )

    INSERT INTO users_interviews (
        interview_id,
        user_id,
        user_role
    )
    VALUES (
        (SELECT rows.interview_id FROM rows),
        $6,
        $8
    )
    RETURNING users_interviews.interview_id INTO _interview_id;
    RETURN _interview_id;
    END
$$ LANGUAGE plpgsql;

While googling the error I saw a suggestion saying I shouldn't use RETURN expression, but RETURN NEXT. I tried it this way, the function does not produce error, but it does not return the id.

DROP FUNCTION IF EXISTS create_bind_interview;
CREATE FUNCTION create_bind_interview(
    VARCHAR(256),
    VARCHAR(256),
    VARCHAR(256),
    VARCHAR(1024),
    VARCHAR(1024),
    BIGINT,
    VARCHAR(64),
    user_roles
)
RETURNS TABLE (interview_id BIGINT) AS $$
DECLARE
    _id BIGINT;
    _interview_free      BOOLEAN;
    _price INT;
    _price_for_interview INT;
    _interview_id BIGINT;
BEGIN
    _id := (SELECT user_id FROM users WHERE email=$1);
    _interview_free := (SELECT free_interview FROM user_settings WHERE user_id = _id);
    _price := (SELECT price FROM user_settings WHERE user_id = _id);
    _price_for_interview := (
        CASE
            WHEN (_interview_free = FALSE) THEN _price
            ELSE 0
        END
    );

    WITH rows AS(
      INSERT INTO interviews (
          interview_price,
          interview_stage,
          interview_date,
          comments,
          topic,
          interview_timezone
      )
      VALUES (
          _price_for_interview,
          $2,
          $3,
          $4,
          $5,
          $7
      )
    RETURNING interviews.interview_id
    )

    INSERT INTO users_interviews (
        interview_id,
        user_id,
        user_role
    )
    VALUES (
        (SELECT rows.interview_id FROM rows),
        $6,
        $8
    )
    RETURNING users_interviews.interview_id;
    RETURN NEXT;
    END
$$ LANGUAGE plpgsql;
0

There are 0 best solutions below