Function not returning result after INSERT

Question

I'm creating a function to insert new data if the data not found. Currently I am unable to return the ID after inserting the data. I get an empty result: return empty id after insert

Here's the query:

CREATE OR REPLACE FUNCTION save_data_if_not_found(
    input_data1 character varying,
    input_data2 character varying,
    input_data3 integer)
    RETURNS SETOF integer
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE 
    ROWS 1000
AS $BODY$
DECLARE save_id integer;
BEGIN
RETURN QUERY    
    SELECT id FROM save_data_if_not_found
        WHERE data2 = input_data2 AND data3 = input_data3;
    IF NOT FOUND THEN
        RAISE INFO 'No id found. Create new';
        INSERT INTO save_data_if_not_found (data1,data2,data3)
            VALUES (input_data1,input_data2,input_data3)
            RETURNING id INTO save_id;
    ELSE
        RAISE INFO 'Id found.';
    END IF;
END;
$BODY$;

SELECT * FROM save_data_if_not_found ('john','doe',1);

See [Returning from function](https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING). 1) The `RETURN QUERY` should be on the `INSERT INTO save_data_if_not_found ...` query. 2) You need a `RETURN;` just before the `END;` 3) You could probably do without the `INTO save_id` as the `id` will be returned from the `INSERT`. — Adrian Klaver, Oct 22 '22 at 16:28

score 0 · Accepted Answer · answered Oct 22 '22 at 18:01

RETURNING id INTO save_id - this is don't return set of records. This code given last inserted id and set it to variable save_id
RETURNING without into - this is gets set of records. And you must write RETURN QUERY clause in the top of INSERT INTO too.

Example:

CREATE OR REPLACE FUNCTION save_data_if_not_found(
    input_data1 character varying,
    input_data2 character varying,
    input_data3 integer)
    RETURNS SETOF integer
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE 
    ROWS 1000
AS $BODY$
DECLARE save_id integer;
BEGIN
RETURN QUERY    
    SELECT id FROM save_data_if_not_found
        WHERE data2 = input_data2 AND data3 = input_data3;
    IF NOT FOUND THEN
        RAISE INFO 'No id found. Create new';
       
        RETURN QUERY
        INSERT INTO save_data_if_not_found (data1,data2,data3)
            VALUES (input_data1,input_data2,input_data3)
            RETURNING id;
    ELSE
        RAISE INFO 'Id found.';
    END IF;
END;
$BODY$;

Function not returning result after INSERT

1 Answers1