SQL数据库函数if/then

Question

在我的supabase数据库中，我有一个单词列表以及它们的uid，格式为

<表类> uuid 词 unique_chars found_count tbody><<tr>x1苹果40y2香蕉30

当您有这些数据时:

CREATE TABLE words (
word VARCHAR(200),
id INTEGER PRIMARY KEY NOT NULL);
INSERT INTO words VALUES
('apple',1),
('banana',2),
('pear',3),
('Pineapple',4);

和这两个函数:

DROP FUNCTION IF EXISTS cnt1(s VARCHAR(200), t varchar(200));
CREATE FUNCTION cnt1(s VARCHAR(200), t VARCHAR(200)) RETURNS INTEGER
AS 'with RECURSIVE cte as (
select 0 as x, $1 as y, $1 as z 
union all 
select x+1,y,substring(y,x+1,1) from cte  where  x<length($1)) 
select count(distinct(z)) from cte where x>0 and position(z in $2)>0;'
LANGUAGE SQL;

DROP FUNCTION IF EXISTS cnt2(s VARCHAR(200));
CREATE FUNCTION cnt2(s VARCHAR(200)) RETURNS INTEGER
AS 'with RECURSIVE cte as (
select 0 as x, $1 as y, $1 as z 
union all 
select x+1,y,substring(y,x+1,1) from cte  where  x<length($1)) 
select count(distinct(z)) from cte where x>0 ;'
LANGUAGE SQL;

你可以这样做:

SELECT
word,
cnt2(word) unique_chars, 
cnt1(word, 'charone') found_count
FROM words;

tbody> <<tr>香蕉菠萝

word	unique_chars	found_count
苹果	4	2
	3	2
梨	4	3
	7	3