Syntax Error while creating function to return json Postgresql

Question

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.

klin · Accepted Answer · 2020-04-11 18:27:34Z

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

Collectives™ on Stack Overflow

Syntax Error while creating function to return json Postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related