How to return a boolean after inserting within a postgres function?

Question

I have a table named member. I want to create a function that inserts a new email if it doesn't exist in the table, and then return a boolean.

CREATE OR REPLACE FUNCTION
  add_member(member_email TEXT)
RETURNS BOOLEAN AS
  $$
    BEGIN
        IF NOT EXISTS (SELECT email FROM "community".member WHERE email = $1) THEN
            INSERT INTO "community".member (email) VALUES ($1) RETURNING TRUE;
        ELSE
            RETURN FALSE;
        END IF;
    END;
  $$
LANGUAGE PLPGSQL;

SELECT add_member('[email protected]');

I get this error ERROR: query has no destination for result data. Is the INSERT not returning the expected data? Or wrapping it in another data type? Is this how you use the INSERT with the returning?

Answer 1

Using INSERT ... RETURNING ... INTO from here:

https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW

CREATE OR REPLACE FUNCTION
  add_member(member_email TEXT)
RETURNS BOOLEAN AS
  $$
    DECLARE
        return_val BOOLEAN := FALSE;
    BEGIN
        IF NOT EXISTS (SELECT email FROM "community".member WHERE email = $1) THEN
            INSERT INTO "community".member (email) VALUES ($1) RETURNING TRUE INTO return_val;
       
        END IF;
    RETURN return_val;
    END;
  $$
LANGUAGE PLPGSQL;

DECLARE the return_val variable as boolean and with default value of FALSE. Then do test for existing value and if present do INSERT and update the return_val variable with the TRUE returning value from the insert. Then RETURN return_val which will be FALSE if an existing member_email is found and TRUE if one is not found.