Returning something like error-codes from query-CodePudding

I want to check conditions in query. If condition is false, then query must returns error code, else query must to execute another query.

Something like that:

WITH error_code_get AS (
    SELECT 
    CASE 
        WHEN NOT EXISTS (
            SELECT 1 
            FROM users 
            WHERE id = '1a4b...'
        ) THEN 1
    
        WHEN NOT EXISTS (
            SELECT 1
            FROM workspaces
            WHERE id = '353...'
        ) THEN 2
    
       WHEN (
            SELECT settings
            FROM workspace_roles 
            WHERE workspace_id = '353...'
            AND id IN (
                SELECT role_id 
                FROM m2m_users_to_workspace_or_projects_roles 
                WHERE role_type='1' 
                AND user_id='1a4b...'
           )
        ) < 2 THEN 3
    
        ELSE 0

    END error_code

    RETURNING error_code

)
// WRONG PART
CASE 
    WHEN (SELECT error_code FROM error_code_get) = 0 THEN (INSERT INTO x(a) VALUES('some_value')) 
    ELSE (SELECT error_code FROM error_code_get)
END

CodePudding user response：

You can't do something like that in pure SQL, you'd need to write code in Stored Proc or Function