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;