时间戳

首页 > 解决方案 > 我如何将此代码转换为 PostgreSQL 存储过程？

postgresql - 我如何将此代码转换为 PostgreSQL 存储过程？

问题描述

我是 PostgreSQL 新手。我想将此代码转换或添加到存储过程。

WITH RECURSIVE t(i) AS (
  SELECT * FROM unnest((select regexp_split_to_array('signature',''))::char[])
), cte AS (
     SELECT i AS combo, i, 1 AS ct 
     FROM t 
   UNION ALL 
     SELECT cte.combo || t.i, t.i, ct + 1
     FROM cte, t
     WHERE ct <= 8
       AND position(t.i in cte.combo) = 0
) 
SELECT distinct cc.combo,ww.word 
FROM cte cc 
   inner join words ww ON ww.word=cc.combo 
WHERE length(combo)>1 
  AND ww.source_id in(1,2,19,21,24,26,33,34) 
ORDER BY cc.combo ASC;

标签： postgresql

解决方案

只需将其放入一个函数中：

create function generate_anagrams(p_word text)
  returns table(combo text, word text)
as
$$
WITH RECURSIVE t(i) AS (
  SELECT * 
  FROM unnest((select regexp_split_to_array(p_word,''))::char[])
), cte AS (
     SELECT i AS combo, i, 1 AS ct 
     FROM t 
   UNION ALL 
     SELECT cte.combo || t.i, t.i, ct + 1
     FROM cte, t
     WHERE ct <= 8
       AND position(t.i in cte.combo) = 0
) 
SELECT distinct cc.combo,ww.word 
FROM cte cc 
   inner join words ww ON ww.word=cc.combo 
WHERE length(combo)>1 
  AND ww.source_id in(1,2,19,21,24,26,33,34) 
ORDER BY cc.combo ASC
$$
language sql;

你可以像这样使用它：

select *
from generate_anagrams('signature');

推荐阅读