By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
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
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);
| find_customer_name_by_email |
|---|
| Vasya |
| Petya |
SELECT 2