PostgreSQL get_records SQL pseudo API function

Question

我正在尝试编写简单的伪 API 函数，使用任何可用列作为属性的简单查询表。

我有一张桌子：

CREATE TABLE card (
id varchar(20) NOT NULL,
type varchar(5) NOT NULL,
id_client int4 NULL,
active bool NOT NULL DEFAULT false,
issue_date date NULL,
valid_date date NULL,
temp bool NULL DEFAULT false)

现在我想要一个函数。每个属性都应该是可选的。

CREATE OR REPLACE FUNCTION get_cards(id varchar,type varchar,id_client int,active bool,issue_date date, valid_date date, temp bool)
RETURNS TABLE (id varchar,type varchar,id_client int,active bool,issue_date date, valid_date date, temp bool) AS
$$
BEGIN
RETURN QUERY SELECT * FROM card WHERE id like $1 AND type like $2 AND [etc.]
END;
$$ LANGUAGE plpgsql;

这应该对以下查询有效：

select get_card('','',17,False,'','','')

Answer 1

如果你来自Oracle，null和空字符串在PostgreSQL中是不一样的。

如果空字符串表示缺少的参数是您真正想要的，那么：

select *
from card
where ($1 = '' or id = $1)
and ($2 = '' or type = $2)
...

如果要用 null 或空字符串来表示缺少的参数：

select *
from card
where (nullif($1, '') is null or id = $1)
and (nullif($2, '') is null or type = $2)
...

Siweq报告说，他使用nullif()调用遵循了该建议，并在将其调用为select get_cards()时收到有关类型不匹配的错误。

如果这是使用方式，则删除nullif()调用并执行以下操作：

CREATE OR REPLACE FUNCTION get_cards(id varchar,type varchar,id_client int,active bool,issue_date date, valid_date date, temp bool)
RETURNS TABLE (id varchar,type varchar,id_client int,active bool,issue_date date, valid_date date, temp bool) AS
$$
BEGIN
RETURN QUERY 
SELECT * FROM card c
WHERE 1=1
AND ($1 IS NULL OR c.id = $1)
AND ($2 IS NULL OR c.type = $2)
AND ($3 IS NULL OR c.id_client = $3)
AND ($4 IS NULL OR c.active = $4)
AND ($5 IS NULL OR c.issue_date = $5)
AND ($6 IS NULL OR c.valid_date = $6)
AND ($7 IS NULL OR c.temp = $7)
END;
$$ LANGUAGE plpgsql;