>Cetin
>
>You are refering to a recursive query, which I (I admit the mischaracterization as recursive CTEs can be use in creating records as well) referred to as for solving hierarchical problems. This is where I almost exclusively use them for.
>
>The challenge was to find me an example where using CTE as a subquery would lead to any significant advantage.
>
>Walter,

I am having a hard time to understand you. Do you mean you are referring recursive CTE as hierarchic? I assume yes. Actually the samples I gave had nothing to do with a hierarchy.

This example is a real world example that I just wrote today (clipped data to 3 rows, original is near 500):

drop table if exists apiList;
create table apiList
(
    id                          serial not null primary key,
    Name                        varchar(200),
    CommandName                 varchar(100),
    Description                 varchar(500),
    Anonymous                   bool,
    publicForAuthenticatedUsers bool,
    requiredParameters          jsonb,
    optionalParameters          jsonb
);

with api(doc) as (
    select *
    from (values (
                     '{
                       "success": true,
                       "results": [
                         {
                           "Name": "Get the documentation",
                           "CommandName": "doc",
                           "Description": "Retrieves the list of actions available in the system.",
                           "Anonymous": true,
                           "PublicForAuthenticatedUsers": false,
                           "RequiredParameters": [],
                           "OptionalParameters": [
                             {
                               "Name": "excludeanonymous",
                               "Description": "If true it excludes the anonymous actions from the returned list.",
                               "TypeName": "boolean"
                             }
                           ]
                         },
                         {
                           "Name": "Lists the available features.",
                           "CommandName": "feature.list",
                           "Anonymous": false,
                           "PublicForAuthenticatedUsers": true,
                           "RequiredParameters": [
                             {
                               "Name": "token",
                               "Description": "The authentication token.",
                               "TypeName": "string"
                             }
                           ],
                           "OptionalParameters": []
                         },
                         {
                           "Name": "Login",
                           "CommandName": "login",
                           "Anonymous": true,
                           "PublicForAuthenticatedUsers": false,
                           "RequiredParameters": [
                             {
                               "Name": "domain",
                               "Description": "The account domain to login.",
                               "TypeName": "string"
                             },
                             {
                               "Name": "username",
                               "Description": "The username (email) of the user to login as.",
                               "TypeName": "string"
                             },
                             {
                               "Name": "password",
                               "Description": "The password of the user to login as.",
                               "TypeName": "string"
                             },
                             {
                               "Name": "method",
                               "Description": "The authentication method. Valid values are ''token'' and ''cookie''.",
                               "TypeName": "string"
                             }
                           ],
                           "OptionalParameters": [
                             {
                               "Name": "rememberme",
                               "Description": "If true then the session will not expire.",
                               "TypeName": "boolean"
                             }
                           ]
                         }
                       ]
                     }'::jsonb
                 )) as api(Doc)
),
     apiList1 (apiJson) as
         (
             select "option"::jsonb
             from api
                , lateral jsonb_each(doc) as t(k, v)
                , lateral jsonb_array_elements_text(t.v) AS tt("option")
             where k = 'results'
         )
insert
into apiList (Name, CommandName, Description, Anonymous, publicForAuthenticatedUsers, requiredParameters,
              optionalParameters)
select apijson ->> 'Name'                                as Name,
       apijson ->> 'CommandName'                         as CommandName,
       apijson ->> 'Description'                         as Description,
       (apijson ->> 'Anonymous')::bool                   as Anonymous,
       (apijson ->> 'PublicForAuthenticatedUsers')::bool as PublicForAuthenticatedUsers,
       apijson #> '{RequiredParameters}'                 as RequiredParameters,
       apijson #> '{OptionalParameters}'                 as OptionalParameters
from APIList1;

select *
from apiList;

I can't paste output as HTML table, here is DML version:

INSERT INTO "MY_TABLE"(id, name, commandname, description, anonymous, publicforauthenticatedusers, requiredparameters, optionalparameters) VALUES (1, 'Get the documentation', 'doc', 'Retrieves the list of actions available in the system.', true, false, '[]', '[{"Name": "excludeanonymous", "TypeName": "boolean", "Description": "If true it excludes the anonymous actions from the returned list."}]');
INSERT INTO "MY_TABLE"(id, name, commandname, description, anonymous, publicforauthenticatedusers, requiredparameters, optionalparameters) VALUES (2, 'Lists the available features.', 'feature.list', null, false, true, '[{"Name": "token", "TypeName": "string", "Description": "The authentication token."}]', '[]');
INSERT INTO "MY_TABLE"(id, name, commandname, description, anonymous, publicforauthenticatedusers, requiredparameters, optionalparameters) VALUES (3, 'Login', 'login', null, true, false, '[{"Name": "domain", "TypeName": "string", "Description": "The account domain to login."}, {"Name": "username", "TypeName": "string", "Description": "The username (email) of the user to login as."}, {"Name": "password", "TypeName": "string", "Description": "The password of the user to login as."}, {"Name": "method", "TypeName": "string", "Description": "The authentication method. Valid values are ''token'' and ''cookie''."}]', '[{"Name": "rememberme", "TypeName": "boolean", "Description": "If true then the session will not expire."}]');