PostgreSQL Question posted in
The official documentation can be found here.

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

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?

Tags:

2

Answers


  1. 0

    read the docs

    just do this in 2 statements.

    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);
            RETURN TRUE;
        ELSE
            RETURN FALSE;
        END IF;
    END;
  $$
LANGUAGE PLPGSQL;

    or

    Read Executing a Command with a Single-Row Result.

    You need to DECLARE a variable to hold the RETURN of the insert.

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search