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 REFCURSOR
LANGUAGE plpgsql
AS $$
DECLARE 
    ref_cursor REFCURSOR := 'customer_cursor';
BEGIN
    OPEN ref_cursor FOR
    SELECT c.name 
    FROM customer AS c
    WHERE c.email = COALESCE(find_customer_name_by_email.email, c.email);

    RETURN (ref_cursor); 
END;$$

CREATE FUNCTION

BEGIN;
SELECT find_customer_name_by_email('petya@email.com');
FETCH 10 from customer_cursor; 
COMMIT;

BEGIN

find_customer_name_by_email

customer_cursor

SELECT 1

name

Petya

FETCH 1

COMMIT

BEGIN;
SELECT find_customer_name_by_email(null);
FETCH 10 from customer_cursor; 
COMMIT;

BEGIN

find_customer_name_by_email

customer_cursor

SELECT 1

name

Vasya

Petya

FETCH 2

COMMIT