Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE customer (
  id SERIAL NOT NULL PRIMARY KEY,
  name TEXT NOT NULL ,
  email TEXT NOT NULL DEFAULT ''
);

CREATE TABLE


INSERT INTO customer(name, email)
VALUES 
  ('Vasya', 'vasya@email.com'), 
  ('Petya', 'petya@email.com');

INSERT 0 2


CREATE OR REPLACE FUNCTION find_customer_name_by_email(email TEXT) 
RETURNS TABLE(name TEXT)
LANGUAGE plpgsql
AS $$
BEGIN
    ASSERT email IS NOT NULL, '"email" parameter cannot be null.';
  
    RETURN QUERY
    SELECT c.name 
    FROM customer AS c
    WHERE c.email = COALESCE(find_customer_name_by_email.email, c.email);
END;$$

CREATE FUNCTION


SELECT find_customer_name_by_email('petya@email.com');


find_customer_name_by_email


Petya



SELECT 1


SELECT find_customer_name_by_email(null);

ERROR:  "email" parameter cannot be null.
CONTEXT:  PL/pgSQL function find_customer_name_by_email(text) line 3 at ASSERT