CODEWITHSUNDEEP
sqljsonpostgresql
Jordan
Jordan

Reputation: 950

Syntax Error while creating function to return json Postgresql

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

Answers (1)

klin
klin

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

Related Questions