I have a recursive query like this:

with recursive PCte(id) as 
(
    select p1.id from Product p1 where p1.parent_id is null and p1.serial_number in
    ('123', '124','125', 'n')
    union all
    select p2.id from Product p2 inner join PCte cte on p2.parent_id = cte.id
) 
select * 
from Product p 
left join Product psub on psub.parent_id = p.id 
where p.id in (select c.id from PCte c)

This query have a slow performance with a large number of children, are there any possible optimization ways? If it is possible to replace the recursive CTE with a temporary table, I would like to see an example, thanks a lot.

I'm trying to recursively loop through and return all child_id's that have the root element of 9. **The structure:** ->9 ->->8 ->->->17 ->->22 ->->->11 **Parent Child Link Table:** +----+-----------+----------+ | id | parent_id | child_id | +----+-----------+----------+ | 1 | 9 | 8 | | 2 | 8 | 17 | | 3 | 8 | 33 | | 4 | 8 | 18 | | 5 | 9 | 22 | | 6 | 22 | 11 | | 7 | 22 | 4 | | 8 | 3 | 5 | +----+-----------+----------+ **Procedure (so far):** BEGIN DECLARE x INT(11) SET x = 0; SET @elements = ""; SET @node = _root_; -- 9 SET @child_count = count_children(@node) -- function returning the child count of @node; SET @children = get_children(@node); -- function returning the child id's of @node -- check IF node has children WHILE x <= @child_count DO SET @elements = CONCAT(@elements,x,','); SET x = x + 1; END WHILE SELECT @elements; END **Desired Output:** [8,17,33,18,22,11,4] **Question:** How can I modify my procedure to be able to return all child_id's of the parent?

I have this query used on Postgres to return place administrative levels.

WITH RECURSIVE hierarchy(name, pinyin, level) AS (
	SELECT p.name, p.pinyin, p.level, p.upper_place_object_id_fk 
    from place p 
    where p.pinyin = 'Jiulong'
	UNION ALL
	SELECT up.name, up.pinyin, up.level, up.upper_place_object_id_fk 
    from hierarchy h
	INNER JOIN place up ON h.upper_place_object_id_fk = up.object_id
	WHERE h.upper_place_object_id_fk IS NOT NULL
)
SELECT h.name, h.pinyin, h.level from hierarchy h;

It works just fine for a single match, e.g.

"Jiulong"	4
"Yingde"	3
"Qingyuan"	2
"Guangdong"	1

But of course, there can be several places with the same name (and a different position in the admin hierarchy)

"Fenggang"	4
"Fenggang"	4
"Huaiji"	3
"Dongguan"	2
"Zhaoqing"	2
"Guangdong"	1
"Guangdong"	1

In this case the result doesn't make sense as I can't reconstruct the proper hierarchy. And that's because the recursion occurs for every result of the parent query at the same time. It'd work if the recursion could be done until the exit condition for each result of the parent. It is possible to control how the recursion is done? Place Table script

CREATE TABLE public.place
(
    object_id uuid NOT NULL DEFAULT uuid_generate_v4(),
    upper_place_object_id_fk uuid,
    name character varying(50) COLLATE pg_catalog."default" NOT NULL,
    level smallint NOT NULL,
    CONSTRAINT pk_place PRIMARY KEY (object_id),
    CONSTRAINT unique_place UNIQUE (name, upper_place_object_id_fk, level)
,
    CONSTRAINT fk_place_upper_pla_place FOREIGN KEY (upper_place_object_id_fk)
        REFERENCES public.place (object_id) MATCH SIMPLE
        ON UPDATE RESTRICT
        ON DELETE RESTRICT
        NOT VALID
)

and here are the relevant data from the table

object_id                               upper_place_object_id_fk                 name      level 
"540ed8e9-c456-4a46-8049-1332f4ab52c1"	NULL									"Guangdong"	1	false
"4f7c7071-f917-4ab8-82c9-75aff0ba44d0"	"540ed8e9-c456-4a46-8049-1332f4ab52c1"	"Dongguan"	2	false
"afce8ec3-599f-4cea-9227-29bb750d4b01"	"540ed8e9-c456-4a46-8049-1332f4ab52c1"	"Zhaoqing"	2	false
"714b8e23-c346-4370-8697-25bb09497342"	"afce8ec3-599f-4cea-9227-29bb750d4b01"	"Huaiji "	3	false
"0461963d-e886-4149-bded-ee08216dd374"	"714b8e23-c346-4370-8697-25bb09497342"	"Fenggang"	4	false
"6955f89b-b7d3-4b1e-b21e-fb584d7f165b"	"712c1f45-24ac-4711-a47f-22513afc6fdd"	"Dongguan"	4	true
"d931f691-0ec7-4fc7-9197-b15461e984e0"	"4f7c7071-f917-4ab8-82c9-75aff0ba44d0"	"Fenggang"	4	true

I am working on a query that performs PostgreSQL CTE Recursion in PostgreSQL 13. This query is intended to recurse from the root nodes to the deepest leaf node with certain properties (labeled 'begat' in the resource_dependency table, with a matching "sample type" property from another table). https://explain.dalibo.com/plan/g2d2f5fg9c563b0d#plan https://explain.depesz.com/s/eaRu It seems like both the CTE table construction and moreso the scanning of the CTE to apply the constraints are the most time consuming parts of the query. Most work appears to be done in memory with the given settings. We still seem to scan 780GB of cache (cache hit) which seems like duplicate work. The actual number of nodes in any given tree may be 1000-5000. In this operation, we are starting from 890 root nodes. There are both 1:many and many:1 relationships in this tree (splitting, recombining) Doing some experiments in a blank environment I notice that: - To get all parent->child resource_ids in single column, we need to do left join so all leaf nodes will have a null child row with their calculated depth. - We track path to ensure we don't get in an infinite cycle. While path is important for avoiding cycles, it it not relevant for the final calculation. Some paths will be duplicating information for the same root -> descendant. This makes the CTE table much larger Are there any techniques that can be used here to make this recursion less painful? We need to calculate the deepest leaf node from the root matching the starting root to only the deepest leaf with the 'begat' relationship and matching 'sample type' property.

i want to calculate or get the price of parent items on bill of materials query here is the fiddle-> https://www.db-fiddle.com/f/o3jLgZxKNLG14mna8QGdVN/6 please note in the second row, betax has qty = 2 and the other parent items could behave the same qty of the first row, always will be 1 **what i need is to get the 'price' and 'subtotal' for every parent**

while doing data collection, I've come to realize that some of the data are missing and could not be re-collected due to some issue with the program. One of the suggestions given was to replace the missing data with dummy data. An example is given below to better explain my issue: the data is intended to contain data from 00:00hrs till 00:00 the next day. However, due to the shortcomings of the program, the data could not be retrieved. Is there a way too use the time in the data column to insert in dummy data? Using the data in the red box as an example, the dummy data would fill up from timings, 00:00hrs - 05:00 and 14:30 - 00:00 on the 15 of Aug. I'm thinking of using CTE to complete the task, however, I'm unsure of how to do it.

I have a table **Category** with a **parent_id** column referencing the self **id** column, for this, I used "ON DELETE CASCADE" meaning if the parent is deleted, other children would be deleted too. At the same table I have a boolean column **active**, is it possible to use **ON UPDATE CASCADE** in order to make the update of this column impact recursively all the children of the updated row? let's take this example **parent > sub1 > sub2**, what is the way (at the database level) to make the update of the column **active** impact all the children? - update the **parent** will impact himself and **sub1** and **sub2** - update the **sub1** will impact himself and the **sub2**. and so on. Thanks in advance.

I have Categories stored in a single table. Where there is no limit on number of childerns. I want to fetch all the linked categories for the provided category id: The reason for getting the hierarchy is that I need to update the path field for each category that is either newly created or updated. I need to maintain the path field Table name: categories id parentId name path isLastLevel 1 0 Cat 1 Cat 1 0 2 1 Cat 2 Cat 1 > Cat 2 0 3 2 Cat 3 Cat 1 > Cat 2 > Cat 3 1 4 0 Cat A Cat A 0 5 4 Cat B Cat A > Cat B 1 Now I want to fetch all the hierarchy for id: 3 What I have tried so far is: with recursive cte (id, name, parentId) AS ( select id, name, parentId from categories where parentId = 1 union all select c.id, c.name, c.parentId from categories c inner join cte on c.parentId = cte.id ) select * from cte; The above query returns: [ { id: 1, parentId: 0, name: Cat 1, path: Cat 1 }, { id: 2, parentId: 1, name: Cat 2, path: Cat 1 > Cat 2 } ] But I want this: [ { id: 1, parentId: 0, name: Cat 1, path: Cat 1 }, { id: 2, parentId: 1, name: Cat 2, path: Cat 1 > Cat 2 }, { id: 3, parentId: 2, name: Cat 3, path: Cat 1 > Cat 2 > Cat 3 } ] If I provide id: 2, in that case I am expecting: [ { id: 1, parentId: 0, name: Cat 1, path: Cat 1 }, { id: 2, parentId: 1, name: Cat 2, path: Cat 1 > Cat 2 } ] There is something that I am doing wrong with the query, can anyone identify? **EDIT** Here is reproduced scenario: https://dbfiddle.uk/llwsSBEu

I am stuck at this. Trying to help PM team to translate a calculation from Excel using a MySQL select query to do reporting. Your help is appreciated. Table has 3 columns - Employee, Month, Salary. Need a MySQL Select statement that also creates a new calculated column Bonus (per employee per month) as below (Logic for the calculated column is in Logic column for reference) :

I need to find the ancestors in a table having an ID value and a Google research point me to recursive Common Table Expression (CTE) I wonder if EXPLAIN can be used to get some insights on how MySQL handles this and if there is room for optimization. Also is this the best strategy to get ancestors in a table? I could involve code in here but don't want to because is gonna lead me to loops and probably multiple SELECT statements sent to the DB. The SQL I wrote following the previous article is as follow:

WITH RECURSIVE page_revisions_path (id, page_id, parent_id) AS
(
 SELECT id, page_id, parent_id
 FROM page_revisions
 WHERE parent_id = 'some_id'
 UNION ALL
 SELECT c.id, c.page_id, c.parent_id 
 FROM page_revisions_path AS cp JOIN page_revisions AS c ON cp.id = c.parent_id
)

SELECT * FROM page_revisions_path;

Here is also the SHOW CREATE TABLE query result:

CREATE_TABLE page_revisions (
 id varchar(26) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
 page_id varchar(26) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 parent_id varchar(26) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 routable tinyint(1) NOT NULL,
 PRIMARY KEY (id),
 KEY IDX1 (page_id),
 KEY IDX2 (parent_id),
 CONSTRAINT FK1 FOREING KEY (parent_id) REFERENCES page_revisions (id),
 CONSTRAINT FK2 FOREING KEY (page_id) REFERENCES pages (id)
) ENGINE=InnoDB

I have a table that references itself: create table tbl ( id int generated by default as identity(start with 100 increment by 1), parent_id int, type tbl_type not null, name text not null, info jsonb, constraint p_tbl primary key (id), constraint f_parent foreign key (parent_id) references tbl(id) on delete cascade ); how can I build a select query that let's me select a record from any level, but also let's me filter the results based on parent record conditions for example, select all the records with type=c, and where root record has type=a and info.test=foo, and direct parent of type=b and info.test=bar from what I read so far, I need to do a recursive join I got it partly to work with: WITH RECURSIVE hierarchy as ( select * from tbl where type="a" and 'info'->>'key1' = 'some_value' -- problem... and type="b" and 'info'->>'key1' = 'different_value' union select c.* from tbl c inner join hierarchy p on p.id = c.parent_id ) SELECT * FROM hierarchy where ... but I cannot get the correct results because of the where condition inside the recursive loop. Say I want all records that have a parent with type=a that has info=foo. This works fine. But then I may want to also filter by another parent with a different prop value, eg. type=b with info=bar. Here if I use "and" it will filter out everything and get me no results. And if I use "or" it will get me all records, including what I don't want... -------------- To summarise, I need this logic in the recursive loop/where: if(type == b) { if(info.key == value) { return true; // this record matches, continue to parent record } else { return false; // break the loop here } } else if (type == a) { // same check as above but with different key and/or value } sqlfiddle: https://sqlfiddle.com/postgresql/online-compiler?id=fc8ca0f6-8e3e-43af-8b0d-cd7c587774ff

I'm using PostgreSQL 17 --- I am modelling a package index for the Haskell ecosystem, and a feature that is useful is to determine transitive dependencies. Haskell packages can be normalised as:

Package 
(name + package-specific metadata)
 \-> Releases 
    (version + release-specific metadata like synopsis, attached data files)
   \-> Components 
      (library, executable, test suite, benchmark suite)
     \-> Dependencies 
        (Each component declares a dependency of a package name and version expression).

(Each of these sections are a table, and they are linked together by one-to-many relationships. One package links to many releases, each release links to many components, each component links to many dependencies) For the purpose of my own enlightenment, I have first reduced the complexity of the model to create a CTE that does what I expect. Especially, I don't use bigints as the PKs of the table in my codebase, but UUIDs. (Full dbfiddle is available at https://dbfiddle.uk/hVOmMdYQ)

-- Data model where packages and versions are combined, 
-- and dependencies refer to packages

create table packages (
  package_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text unique not null,
  version int[] not null
);

create unique index on packages(name, version);

create table dependencies (
  dependency_id bigint generated always as identity PRIMARY KEY,
  dependent_id bigint references packages(package_id),
  depended_id bigint references packages(package_id) 
);

create unique index on dependencies(dependent_id, depended_id);

And here is the data:

insert into packages (name, version) values ('base', '{1,0,0,0}');
insert into packages (name, version) values ('vector', '{0,0,7,0}');
insert into packages (name, version) values ('random', '{0,1,5,8}');
insert into packages (name, version) values ('unix', '{1,2,1,0}');
insert into packages (name, version) values ('time', '{3,14,1,2}');

insert into dependencies (dependent_id, depended_id) values (2, 1);
insert into dependencies (dependent_id, depended_id) values (3, 1);
insert into dependencies (dependent_id, depended_id) values (3, 2);
insert into dependencies (dependent_id, depended_id) values (4, 1);
insert into dependencies (dependent_id, depended_id) values (5, 1);
insert into dependencies (dependent_id, depended_id) values (5, 3);
insert into dependencies (dependent_id, depended_id) values (5, 4);

Here is a preliminary result:

select dependent.package_id, dependent.name as dependent, depended.name as depended
  from dependencies as d1
  inner join packages as dependent on d1.dependent_id = dependent.package_id
  inner join packages as depended  on d1.depended_id = depended.package_id;

| package_id | dependent | depended | |-----------:|-----------|----------| | 2 | vector | base | | 3 | random | base | | 3 | random | vector | | 4 | unix | base | | 5 | time | base | | 5 | time | random | | 5 | time | unix | Until now, every looks good. I then made this recursive CTE to create a view of transitive dependencies, with breadcrumbs:

with recursive transitive_dependencies ( dependent_id, dependent, depended_id, breadcrumbs) as 
( select dependent.package_id as dependent_id
       , dependent.name as dependent
       , depended.package_id as depended_id
       , concat_ws(' > ', dependent.name, depended.name) as breadcrumbs
  from dependencies as d1
    inner join packages as dependent on d1.dependent_id = dependent.package_id
    inner join packages as depended  on d1.depended_id = depended.package_id
    where dependent_id = 5

  union all
  
  select dependent.package_id as dependent_id
       , dependent.name as dependent
       , depended.package_id as depended_id
       , concat_ws(' > ', t2.breadcrumbs, depended.name) as breadcrumbs   

  from dependencies as d1
     inner join packages as dependent on d1.dependent_id = dependent.package_id
     inner join packages as depended  on d1.depended_id = depended.package_id
     inner join transitive_dependencies as t2 on t2.depended_id = dependent.package_id -- ← This is where we refer to the CTE
)

  cycle dependent_id set is_cycle using path
 
select t3.dependent_id
     , t3.dependent
     , t3.depended_id
     , t3.breadcrumbs
from transitive_dependencies as t3;

| dependent_id | dependent | depended_id | breadcrumbs | |-------------:|-----------|------------:|-------------------------------| | 5 | time | 1 | time > base | | 5 | time | 3 | time > random | | 5 | time | 4 | time > unix | | 3 | random | 1 | time > random > base | | 3 | random | 2 | time > random > vector | | 4 | unix | 1 | time > unix > base | | 2 | vector | 1 | time > random > vector > base | Behold, it works! --- Now, I am looking into splitting things a bit further. Namely, package and release will be separated. This is due to the fact that there is some metadata specific to the Haskell ecosystem that targets the notion of "package" and some that is only relevant to "releases", and they are not interchangeable.

-- Data model where packages and releases are separated

create table packages2 (
  package_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text unique not null
);

create table releases2 (
  release_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  package_id bigint references packages2,
  version text not null
);

create unique index on releases2(package_id, version);

create table dependencies2 (
  dependency_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  release_id bigint references releases2 not null,
  package_id bigint references packages2 not null,
  requirement int[] not null
);

And here is the data

insert into packages2 (name) values ('base'); -- 1
insert into packages2 (name) values ('vector'); -- 2
insert into packages2 (name) values ('random'); -- 3
insert into packages2 (name) values ('unix'); -- 4
insert into packages2 (name) values ('time'); -- 5

insert into releases2 (package_id, version) values (1, '{1,0,0,0}');
insert into releases2 (package_id, version) values (2, '{0,0,7,0}');
insert into releases2 (package_id, version) values (3, '{0,1,5,8}');
insert into releases2 (package_id, version) values (4, '{1,2,1,0}');
insert into releases2 (package_id, version) values (5, '{3,14,1,2}');


insert into dependencies2 (release_id, package_id, requirement) values ( 2, 1, '== 1.0.0.0' );
insert into dependencies2 (release_id, package_id, requirement) values ( 3, 1, '== 1.0.0.0' );
insert into dependencies2 (release_id, package_id, requirement) values ( 3, 2, '>= 0.0.7.0' );
insert into dependencies2 (release_id, package_id, requirement) values ( 4, 1, '== 1.0.0.0' );
insert into dependencies2 (release_id, package_id, requirement) values ( 5, 1, '== 1.0.0.0' );
insert into dependencies2 (release_id, package_id, requirement) values ( 5, 3, ' ', p2.name, p3.name) as breadcrumbs
  from dependencies2 as d0
    -- Dependent releases
    inner join releases2 as r1 on d0.release_id = r1.release_id
    -- Dependent packages
    inner join packages2 as p2 on r1.package_id = p2.package_id
    -- Dependencies packages
    inner join packages2 as p3 on d0.package_id = p3.package_id
  where r1.release_id = 5

  union

  select p2.package_id as dependent_id
       , p2.name as dependent
       , p3.package_id as dependency_id
       , concat_ws(' > ', p2.name, p3.name) as breadcrumbs
  from dependencies2 as d0
    -- Dependent releases
    inner join releases2 as r1 on d0.release_id = r1.release_id
    -- Dependent packages
    inner join packages2 as p2 on r1.package_id = p2.package_id
    -- Dependencies packages
    inner join packages2 as p3 on d0.package_id = p3.package_id
    inner join transitive_dependencies2 as t2 on t2.dependency_id = p2.package_id ← This is where we refer to the CTE
)

    cycle dependent_id set is_cycle using path

select t3.dependent_id
     , t3.dependent
     , t3.dependency_id
     , t3.breadcrumbs
from transitive_dependencies2 as t3;

Quite unfortunately, this does not give the expected result: | dependent_id | dependent | dependency_id | breadcrumbs | |-------------:|-----------|--------------:|-----------------| | 5 | time | 1 | time > base | | 5 | time | 3 | time > random | | 5 | time | 4 | time > unix | | 3 | random | 1 | random > base | | 3 | random | 2 | random > vector | | 4 | unix | 1 | unix > base | | 2 | vector | 1 | vector > base | My question is as follow: How can I build my intuition to further split a CTE that works, over more granular tables? I'm still very new to all of this, and this is my first "real-world" use case of CTEs. Happy to clarify or disambiguate things. Through this, I'm also interested in best practices when it comes to data modelling. I was warned in the past against storing arrays of foreign keys, for instance, and to strive and reach normal forms and splitting entities that have different life cycles.

Let's take into consideration the following tables: CREATE TABLE actions ( id BIGINT(20) unsigned PRIMARY KEY NOT NULL AUTO_INCREMENT, name VARCHAR(255) NOT NULL, user_id BIGINT(20) unsigned NOT NULL ); CREATE TABLE recurring_actions ( original_action_id BIGINT(20) unsigned NOT NULL, recurring_action_id BIGINT(20) unsigned NOT NULL ); The data in each table just for an example is as follows: actions id name user_id 1 fdfdk 3 2 43434 3 3 43334 5 4 sdkk 6 5 zz 7 6 ll 3 recurring_actions original_action_id recurring_action_id 1 2 4 6 2 3 3 5 How can someone query and fetch all the chain of recurring action ids that lead to the last child with id 5 ? Expected result should be [1, 2, 3, 5] (including 5 is ok) I can solve this so far only by recursive querying by application code. Get the original action then if found, query again and so on. Recursive consecutive queries initiated by PHP/C# or whatever code used. I want to do this instead in one recursive (or other solution) MySQL query The answer should focus only on a query solution (if possible) and not in organizing the database in another way. I am aware of other possible database designs which are more suitable for child parent relationships (such as closure tables, nested sets etc).

I am using redshift to determine projected inventory for next few week. I have receipts, current inventory and forecast. ei= ei(previous row) + receipts - forecast wk bi r f ei 1 100 20 80 40 2 50 0 3 30 15 15 4 40 14 41 5 10 31 create table ra_analytics.Test_1Test (wk int, bi int ,r int ,f int ,ei int); insert into ra_analytics.Test_1Test values (1, 100 ,20 ,80 ,0); insert into ra_analytics.Test_1Test values (2, 0 ,0 ,50 ,0); insert into ra_analytics.Test_1Test values (3, 0 ,30 ,15 ,0); insert into ra_analytics.Test_1Test values (4, 0 ,40 ,14 ,0); insert into ra_analytics.Test_1Test values (5, 0 ,0 ,10 ,0); select * from ra_analytics.test_1test order by wk --Update first week of inventory update ra_analytics.test_1test set Ei= bi+r-f from ra_analytics.test_1test where wk=1 --for next weeks inventory using lag function select wk, bi, r, f, lag(ei,1) over(order by wk) +r -f as endinv from ra_analytics.test_1test order by wk

I am looking for examples of _why_ you would use a recursive CTE, but the dozens of examples I have found in my web searching basically reduce to two: - Generating a sequence - Iterating through an employee hierarchy I did find one example where a recursive CTE is used to split a string by commas, which is by far the most interesting (dbfiddles: SQL Server , Postgres ). Are there examples of recursive CTEs which are not basically one of the above?

I have written a PostgreSQL query that's working perfectly: ~~~pgsql WITH RECURSIVE x AS ( SELECT i, parent, id, name, type, '' AS path FROM entry WHERE name = 'JS-VBNET-2' UNION ALL SELECT e.i, e.parent, e.id, e.name, e.type, (x.path || '/' || e.name) AS path FROM entry e, x WHERE x.id = e.parent ) SELECT x.i as reti, x.id as retid, x.name as retname, types.mime as retmime, x.path as retpath FROM x JOIN types ON types.i = x.type WHERE x.path = '/Index.htm'; ~~~ I tried to convert this query to a function: ~~~pgsql -- DROP FUNCTION path1(character varying,character varying); CREATE OR REPLACE FUNCTION path1(enter character varying, request character varying) RETURNS TABLE ( reti INTEGER, retid character varying, retname character varying, retmime character varying, retpath character varying ) AS $$ BEGIN WITH RECURSIVE x AS ( SELECT i, parent, id, name, type, '' AS path FROM entry WHERE name = enter UNION ALL SELECT e.i, e.parent, e.id, e.name, e.type, (x.path || '/' || e.name) AS path FROM entry e, x WHERE x.id = e.parent ) SELECT x.i as reti, x.id as retid, x.name as retname, types.mime as retmime, x.path as retpath FROM x JOIN types ON types.i = x.type WHERE x.path = request; END; $$ LANGUAGE plpgsql; GRANT EXECUTE ON FUNCTION path1(enter character varying, request character varying) TO public; ~~~ Call: select * from public.PATH1 ('JS-VBNET-2', '/Index.htm'); But I get an error: > ~~~none > ERROR: 42601: query has no destination for result data > HINT: If you want to discard the results of a SELECT, use PERFORM instead. > CONTEXT: PL/pgSQL function path1(character varying,character varying) line 3 at SQL statement > ~~~ AI hint is: > Add a destination for the result data, such as using INTO or returning the results in a SELECT statement. What is going wrong? How I can convert my function correctly?

I was able to put together a rough idea for a PostgreSQL query to query over a node table, which contains id, parent__id, slug, and file_url (optional). It's considered a file if it has file_url, otherwise it's a directory. -- Enable the pg_trgm extension for fuzzy matching CREATE EXTENSION IF NOT EXISTS pg_trgm; WITH RECURSIVE path_cte AS ( -- Base case: Start with root nodes SELECT id, slug, parent__id, slug AS path FROM node WHERE parent__id IS NULL -- Start from root nodes UNION ALL -- Recursive case: find children using similarity matching with parent path SELECT n.id, n.slug, n.parent__id, CONCAT(pc.path, '/', n.slug) AS path FROM node n JOIN path_cte pc ON n.parent__id = pc.id AND similarity(n.slug, pc.slug) > 0.3 -- Adjust the threshold as needed ) -- Select the final paths for each node SELECT id, path FROM path_cte ORDER BY path; This is where the AI and I got. However, we duly note: > Keep in mind that the similarity condition might introduce cases where nodes do not match their parent due to the similarity threshold. If no similar match is found at a given level, recursion will stop for that branch. In VSCode, I can search like this: Or even: How can I replicate that in PostgreSQL? Given my hierarchical nodes table: CREATE TABLE nodes ( id SERIAL PRIMARY KEY, -- Unique identifier for each node slug VARCHAR(255) NOT NULL, -- Name or slug of the node parent__id INT REFERENCES nodes(id) ON DELETE CASCADE, -- Parent node ID, referencing the same table file_url VARCHAR(255) -- Optional URL for file associated with the node ); Or if that is not a good table structure, then what is a good one to be able to search like this in PostgreSQL? Ideally a user can pass in a path like a Unix file path, and perhaps it splits it at the / slashes (or not), and then does a fuzzy search on each segment relative to the parent, recursively, without finding things that don't match, against a PostgreSQL schema. Can it be done? If so, how? If not, where does it become impossible, and what is possible close to this? Otherwise, this is what I'm doing in JS: export async function search({ searchTerm }: { searchTerm: string }) { return await db .selectFrom('image') .select('path') .where( sql`similarity(path, ${sql.lit( searchTerm, )}::text) > ${sql.lit(similarityThreshold)}::float`, ) .orderBy( sqlsimilarity(path, ${sql.lit(searchTerm)}::text), 'desc', ) .execute() } Doesn't seem like that will cut it.

I've got a simple parent-child tree relationship that was built recursively | Parent | Child | | -------- |-------------- | | A | B | | B | E | | B | C | | E | NULL | | C | NULL | | A | F | | F | NULL | this table comes from a recursive CTE:

with tree(parent, child) as (...)
select * from tree

I want to pivot it into | Parent| Child1 | Child2 | | -------- |-----|--------- | | A | B | E | | A | B | C | | A | F | | I've achieved this with a series of hardcoded joins, is something like this possible with somehow a dynamic pivot up to the Nth Child? The current code is something like this:

with tree(parent, child) as (...)
select 
   P.parent, P.child as child1, C2.child as child2, C3.child as child3 
from tree as P
left join tree as C2
 on P.child = C2.parent
left join tree as C3
 on C2.child = C3.parent

i have data look like this : i try to sum the Nominal column to each respective parent, so it will looks like this : i provided the sql script for the data :

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Account](
    [AccountSeq] [bigint] IDENTITY(1,1) NOT NULL,
    [ParentAccountSeq] [bigint] NULL,
    [AccountCode] [nvarchar](50) NOT NULL,
    [AccountName] [nvarchar](50) NOT NULL,
    [Nominal] [decimal](18, 2) NOT NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Account] ON 
GO
INSERT [dbo].[Account] ([AccountSeq], [ParentAccountSeq], [AccountCode], [AccountName], [Nominal]) VALUES (1, NULL, N'101', N'AKTIVA LANCAR', CAST(0.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[Account] ([AccountSeq], [ParentAccountSeq], [AccountCode], [AccountName], [Nominal]) VALUES (2, 1, N'10101', N'KAS', CAST(0.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[Account] ([AccountSeq], [ParentAccountSeq], [AccountCode], [AccountName], [Nominal]) VALUES (3, 2, N'1010101', N'KAS KECIL (IDR)', CAST(10000.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[Account] ([AccountSeq], [ParentAccountSeq], [AccountCode], [AccountName], [Nominal]) VALUES (4, 2, N'1010102', N'KAS KECIL ($$$)', CAST(15000.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[Account] ([AccountSeq], [ParentAccountSeq], [AccountCode], [AccountName], [Nominal]) VALUES (5, 1, N'10102', N'BANK', CAST(0.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[Account] ([AccountSeq], [ParentAccountSeq], [AccountCode], [AccountName], [Nominal]) VALUES (6, 5, N'1010201', N'BCA PKU AC: 220.391', CAST(20000.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[Account] ([AccountSeq], [ParentAccountSeq], [AccountCode], [AccountName], [Nominal]) VALUES (7, 5, N'1010202', N'BCA PKU AC: 220.279', CAST(25000.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[Account] ([AccountSeq], [ParentAccountSeq], [AccountCode], [AccountName], [Nominal]) VALUES (8, 1, N'10104', N'PIUTANG USAHA', CAST(30000.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[Account] ([AccountSeq], [ParentAccountSeq], [AccountCode], [AccountName], [Nominal]) VALUES (10, 1, N'10105', N'PIUTANG PROYEK', CAST(40000.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[Account] ([AccountSeq], [ParentAccountSeq], [AccountCode], [AccountName], [Nominal]) VALUES (11, NULL, N'201', N'HUTANG JANGKA PENDEK', CAST(50000.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[Account] ([AccountSeq], [ParentAccountSeq], [AccountCode], [AccountName], [Nominal]) VALUES (12, NULL, N'301', N'MODAL', CAST(60000.00 AS Decimal(18, 2)))
GO
SET IDENTITY_INSERT [dbo].[Account] OFF
GO

the last sql query i tried :

WITH cteTest AS (
    SELECT
    AccountSeq,
    ParentAccountSeq,
    AccountCode,
    AccountName,
    CAST(Nominal AS DECIMAL(18,2)) AS Nominal
    FROM
    [Account2]
    WHERE
    ParentAccountSeq IS NULL

    UNION ALL

    SELECT
    a.AccountSeq,
    a.ParentAccountSeq,
    a.AccountCode,
    a.AccountName,
    CAST((a.Nominal + cte.Nominal) AS DECIMAL(18,2)) AS Nominal
    FROM
    [Account2] a
    INNER JOIN [cteTest] cte ON cte.AccountSeq = a.ParentAccountSeq
)
SELECT
*
FROM
[cteTest]
ORDER BY
AccountSeq

Please can anyone help me to solve this?

This question is about SQL in general. Answering specifically for MySQL would be helpful but not necessary. --- Ok, I’m having trouble putting this into words… so bear with me. Say I have a tree of things (I’ll call them nodes), with a table that looks something like this (the structure can be changed; this is just a simple version): +---------+-----------+ | node_id | parent_id | +---------+-----------+ | 1 | NULL | | 2 | NULL | | 3 | 1 | | 4 | 1 | | 5 | 5 | | 7 | 2 | | 8 | 5 | +---------+-----------+ Then I have a table of entities. Each entity is associated with a certain node. For example (again, the structure can be changed): +-----------+---------+ | entity_id | node_id | +-----------+---------+ | 1 | 1 | | 2 | 1 | | 3 | 4 | | 4 | 7 | | ...many more rows | +-----------+---------+ This structure could represent a lot of things, e.g. each entity is a movie, while each node is a genre (but there can be unlimited levels of sub-genres). So retrieving all the entities for a given node is simple; just running a query on the entities table for a specified node_id. Here’s my question: how would I query the entities table for all entities associated with a given node, *and* all it’s children nodes (every level, recursively). In the movies example, I want to find all movies for a particular genre and all its sub-genres, and its sub-genres, etc. I mentioned the word recursive, but that doesn’t mean that the query should be recursive, but conceptually it is. The query should be as fast as possible. The structure of the tables may need to be changed as well. Thanks for your help!