So the three questions I'm hoping someone can answer are:
1.Should I be using a CURSOR here or is there an alternative that will get the same recursive result?
No, you should not. The CURSOR keep being opened over and over. Thinking of the overhead make me cringe. Personally, I stay away from CURSORs.
2.How can I get the results back in a single result set so that it can be used in the same fashion as a subselect?
3.What is the proper way of using the results from the CALL because I can't at least as far as I've tried get the sample SELECT statement above to work? I believe this is because I can't use CALL inline but I'm not sure.
I would like to bail you out of this by suggesting something I learned from my college days when learning about data structures. You need to perform tree traversal using a queue. This allows you to start at a root and express all descendants of a tree.
The algorithm goes like this
- STEP 01 : Start with an empty queue
- STEP 02 : Dequeue Node From the Front of the Queue
- STEP 03 : Enqueue All Children of the Latest Node
- STEP 04 : Process Info From the Latest Node
- STEP 05 : If the Queue is Not Empty, Go Back to STEP 02
- STEP 06 : All Done
This allows you to traverse a recursive structure without using Programmatic Recursion. At this point, you are probably asking: How can I traverse a tree structure without recursion?
I wrote a post about how to script three Stored Procedures that can using a loop in a single CALL that will traverse a table with nodes and its parent in a table:
- GetParentIDByID
- GetAncestry
- GetFamilyTree
The post is Find highest level of a hierarchical field: with vs without CTEs (Oct 24, 2011). It contains the Stored Procedures already written that will traverse the following table structure:
+------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| parent_id | int(11) | YES | | NULL | |
| name | varchar(255) | YES | | NULL | |
| notes | text | YES | | NULL | |
+------------+--------------+------+-----+---------+----------------+
Please read the code carefully and apply the principles.
Give it a Try !!!
First off, you do not want to use char(50)
. Use varchar(50)
or just text
. Read more:
Assuming the following rules:
- Basic slugs never end with a dash.
- Duplicate slugs are suffixed with a dash and a sequential number (
-123
).
Note that all of the following methods are subject to a race conditions: concurrent operations might identify the same "free" name for the next slug.
To defend against it, you can impose a UNIQUE constraint on slug
and be prepared to repeat an INSERT upon duplicate key violation or you to take out a write lock on the table at the start of the transaction.
If you glue the suffix to the basic slug name with a dash and allow basic slugs to end in separate numbers, the specification is a tiny bit ambiguous (see comments). I suggest a unique delimiter of your choice instead (which is otherwise disallowed).
Efficient rCTE
WITH RECURSIVE
input AS (SELECT 'news-on-apple'::text AS slug) -- input basic slug here once
, cte AS (
SELECT slug || '-' AS slug -- append '-' once, if basic slug exists
, 1 as suffix -- start with suffix 1
FROM article
JOIN input USING (slug)
UNION ALL
SELECT c.slug, c.suffix + 1 -- increment by 1 ...
FROM cte c
JOIN article a ON a.slug = c.slug || c.suffix -- ... if slug-n already exists
)
(
SELECT slug || suffix AS slug
FROM cte
ORDER BY suffix DESC -- pick the last (free) one
LIMIT 1
) -- parentheses required
UNION ALL -- if the basic slug wasn't taken, fall back to that
SELECT slug FROM input
LIMIT 1;
Better performance without rCTE
If you worry about thousands of slugs competing for the same slug or generally want to optimize performance, I'd consider a different, faster approach.
WITH input AS (SELECT 'news-on-apple'::text AS slug
, 'news-on-apple-'::text AS slug1) -- input basic slug here
SELECT i.slug
FROM input i
LEFT JOIN article a USING (slug)
WHERE a.slug IS NULL -- doesn't exist yet.
UNION ALL
( -- parentheses required
SELECT i.slug1 || COALESCE(right(a.slug, length(i.slug1) * -1)::int + 1, 1)
FROM input i
LEFT JOIN article a ON a.slug LIKE (i.slug1 || '%') -- match up to last "-"
AND right(a.slug, length(i.slug1) * -1) ~ '^\d+$' -- suffix numbers only
ORDER BY right(a.slug, length(i.slug1) * -1)::int DESC
)
LIMIT 1;
If the basic slug isn't taken yet, the more expensive second SELECT
is never executed - same as above, but much more important here. Check with EXPLAIN ANALYZE
, Postgres is smart that way with LIMIT
queries. Related:
Check for the leading string and the suffix separately, so the LIKE
expression can use a basic btree index with text_pattern_ops
like
CREATE INDEX article_slug_idx ON article (slug text_pattern_ops);
Detailed explanation:
Convert the suffix to integer before you apply max()
. Numbers in text representation don't work.
Optimize performance
To get the optimum, consider storing the suffix separated from the basic slug and concatenate the slug as needed: concat_ws('-' , slug, suffix::text) AS slug
CREATE TABLE article (
article_id serial PRIMARY KEY
, title text NOT NULL
, slug text NOT NULL
, suffix int
);
The query for a new slug then becomes:
SELECT slug
|| COALESCE((
SELECT '-'::text || (max(suffix) + 1)::text
FROM article a
WHERE a.slug = i.slug), '') As slug
FROM (SELECT 'news-on-apple'::text AS slug) i -- input basic slug here
Ideally supported with a unique index on (slug, suffix)
.
Query for list of slugs
In any version of Postgres you can provide rows in a VALUES
expression.
SELECT *
FROM article
JOIN (
VALUES
('slug-foo'::text, 1)
('slug-bar',7)
) u(slug,suffix) USING (slug,suffix);
You can also use IN
with a set of row-type expressions Which is shorter:
SELECT *
FROM article
WHERE (slug,suffix) IN (('slug-foo', 1), ('slug-bar',7));
Details under this related question (as commented below):
For long lists, the JOIN
to a VALUES
expression is typically faster.
In Postgres 9.4 (released today!) you can also use the new variant of unnest()
to unnest multiple arrays in parallel.
Given an array of basic slugs and a corresponding array of suffixes (as per comment):
SELECT *
FROM article
JOIN unnest('{slug-foo,slug-bar}'::text[]
, '{1,7}'::int[]) AS u(slug,suffix) USING (slug,suffix);
Best Answer
I think what you want can be achieved by adding in the column list a "depth" or "level" that increases by one for each recursion: