>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.
I was referring to situations where I use a CTE to solve hierarchical problems through recursion.
You outlined that you can use recursive CTE's to generate rows, which indeed was something I overlooked in my initial reply and should have stated that in the three types of CTE's
1. Recursive (which previously I referred to as solving hierarchical problems)
2, To use a CTE multiple times in a Query
3. As a subquery
Did I forget one ??
As I said,
For type 1, you absolutely want to use CTE's.
For type 2, you might want to use them to cut code and reduce potential errors. However I would say that if the CTE is something is a univerally known entity (like invoice totals, order history) you might want to consider to use views or inline functions as you can easily re-use them for other queries and maintain them in one single place. CTEs are often used where views or inline functions are a better fit.
For Type 3 where a CTE is only used once as a subquery, I stay with my opinion they are overused. It seldomly makes a query more readable and it most cases it results in a longer statement.
As for your example below, I wonder why you would throw me a Postgress SQL statement with json in it. I'm not doing postgress and I don't know the exact datatypes and syntax of its json handling, so your message gets lost here. why is this an example of something you cannot do or less efficient in a subquery?
And perhaps, I would even agree this is an applicable case to use it because of seperating the generation of values and the actual query (a big difference with using CTEs as substitution of subqueries). But this is not the typical case of CTEs we were discussing here. We were discussing the overuse of a CTE in type 3 queries like the one you initially wrote in at the beginning of this thread.
>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."}]');
>
