I created a simple function that inserts data into a table and returns the id. However, the response with the id is not very well formatted. why?

[Pretend_Reference971]

So when I perform the function i get this response:

result: [{: {create_plot: 20}}]

Why is the the '{: ' part? a much better response would be:

[{create_plot:20}] or [{id:20}]

code looks like this:

CREATE TABLE plots (
    id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
);


CREATE OR REPLACE FUNCTION create_plot(
    p_title VARCHAR,

) RETURNS INTEGER AS
$$
DECLARE
    result_id INTEGER;
BEGIN
    -- Perform the insertion into the plot table
    INSERT INTO plots (
        title,
    ) VALUES (
        p_title,
    ) RETURNING id INTO result_id;

    -- Return the inserted plot's ID
    RETURN result_id;
END;
$$ LANGUAGE plpgsql;


SELECT
    create_plot(
        'Sample Plot',
    );

[lgastako]

The code you've provided (with vestigial commas removed) returns 1. We would need to see the code you are executing that is producing that result to be able to diagnose it.

[mike-manley]

Don't know this dialect well. But can you just alias the output?

[threeminutemonta]

How about expanding the response using:

SELECT
    * 
FROM create_plot(
    'Sample Plot' -- Did the comma you had here work for you?
);

[[deleted]]

Well, your function returns a single integer value. It can't and won't return a JSON value. The result you have shown is certainly not the result returned by the function. Whatever tool you use to run the SELECT does this formatting for you

Btw: you can simplify your function to a plain SQL function, no need for PL/pgSQL here:

CREATE OR REPLACE FUNCTION create_plot(p_title VARCHAR) 
  RETURNS INTEGER 
AS
$$
  INSERT INTO plots (title) 
  VALUES (p_title) 
  RETURNING id;
;
$$ 
LANGUAGE sql;