Reputation: 950
I keep getting a syntax error while trying to create this function, and I can't figure out what's going on.
Code:
CREATE OR REPLACE FUNCTION avadi_enterprise.create_doctor(_form json)
RETURNS json
LANGUAGE plpgsql
COST 100
VOLATILE
AS $$
BEGIN
RETURN(
SELECT json_agg(results)
FROM(
INSERT INTO avadi_enterprise.doctor(
first_name,
last_name,
phone_number,
email,
office_open,
office_close,
shipping_route_id,
sales_rep_id,
user_login_id,
status)
VALUES
(_form::json ->> 'first_name',
_form::json ->> 'last_name',
_form::json ->> 'phone_number',
_form::json ->> 'email',
CAST(_form::json ->> 'office_open' AS time),
CAST(_form::json ->> 'office_close' AS time),
CAST(_form::json ->> 'shipping_route_id' AS int),
CAST(_form::json ->> 'sales_rep_id' AS int),
CAST(_form::json ->> 'user_login_id' AS int),
_form::json ->> 'status')
RETURNING
*)
AS results)
END;
$$;
Error:
ERROR: syntax error at or near "INTO"
LINE 18: INSERT INTO avadi_enterprise.doctor(
^
SQL state: 42601
Character: 310
I've tried putting a semicolon after RETURNING *
but then it says I have a mismatched parenthesis.
Postgresql is version 12.
Upvotes: 1
Views: 218
Reputation: 121889
You cannot place an INSERT
statement in the FROM
clause. Use a WITH
statement instead. The function is actually a single query so an SQL
function would be more appropriate:
CREATE OR REPLACE FUNCTION avadi_enterprise.create_doctor(_form json)
RETURNS json
LANGUAGE SQL
COST 100
VOLATILE
AS $$
WITH results AS (
INSERT INTO avadi_enterprise.doctor(
first_name,
last_name,
phone_number,
email,
office_open,
office_close,
shipping_route_id,
sales_rep_id,
user_login_id,
status)
VALUES(
_form::json ->> 'first_name',
_form::json ->> 'last_name',
_form::json ->> 'phone_number',
_form::json ->> 'email',
CAST(_form::json ->> 'office_open' AS time),
CAST(_form::json ->> 'office_close' AS time),
CAST(_form::json ->> 'shipping_route_id' AS int),
CAST(_form::json ->> 'sales_rep_id' AS int),
CAST(_form::json ->> 'user_login_id' AS int),
_form::json ->> 'status')
RETURNING *
)
SELECT json_agg(results)
FROM results
$$;
Upvotes: 1