Feature Request/Discussion: CTE Persistence Across SELECT Statements

[amrutadotorg]

Summary

Hi, @lovasoa currently, CTEs (Common Table Expressions) defined in one SELECT statement don't persist to subsequent SELECT statements in the same SQLPage file. This leads to significant code duplication when the same CTE logic needs to be used across multiple components.

Current Behavior

-- This doesn't work in SQLPage
WITH api_data AS (
    SELECT json_extract($api_response, '$.title') AS title,
           json_extract($api_response, '$.content') AS content
)

-- Component 1
SELECT 'text' AS component, title FROM api_data;

-- Component 2 - ERROR: no such table: api_data
SELECT 'card' AS component, content FROM api_data;

Error: error returned from database: (code: 1) no such table: api_data

Current Workarounds

1. Repeat the CTE in every query (verbose)

WITH api_data AS (SELECT ...) SELECT ... FROM api_data;
WITH api_data AS (SELECT ...) SELECT ... FROM api_data;  -- Duplicate!
WITH api_data AS (SELECT ...) SELECT ... FROM api_data;  -- Duplicate!

2. Use SET variables (limited to scalar values)

SET title = json_extract($api_response, '$.title');
SET content = json_extract($api_response, '$.content');

SELECT 'text' AS component, $title AS title;
SELECT 'card' AS component, $content AS content;

This works for simple values but doesn't help with:

• Complex transformations
• Aggregations
• Joins that need to be reused
• Arrays/JSON structures

Use Case: API Response Parsing

A common pattern in SQLPage is fetching JSON from an API and displaying it across multiple components:

SET api_response = (SELECT sqlpage.fetch('https://api.example.com/data'));

-- Ideally, we'd want to parse once:
WITH parsed_data AS (
    SELECT 
        json_extract($api_response, '$.title') AS title,
        json_extract($api_response, '$.metadata.country') AS country,
        json_extract($api_response, '$.metadata.category') AS category,
        json_extract($api_response, '$.media.vimeo_url') AS vimeo_url
        -- ... 20+ more fields
),
translations AS (
    SELECT translation_key, translation_value 
    FROM language 
    WHERE language_code = $lang
)

-- Then use across many components
SELECT 'datagrid' AS component, title FROM parsed_data;
SELECT 'map' AS component, country FROM parsed_data;
SELECT 'video' AS component, vimeo_url FROM parsed_data;
-- ... 10+ more components

Currently, we must either:

1. Repeat both CTEs in every single SELECT statement (10+ times)
2. Use SET variables for each field (20+ SET statements)
3. Repeat json_extract($api_response, '...') throughout (60+ calls)

Proposed Solution

Allow CTEs to persist for the current file execution scope, similar to how SET variables work:

-- Define once at the top
WITH PERSISTENT api_data AS (...),
     PERSISTENT translations AS (...)

-- Use throughout the file
SELECT ... FROM api_data;
SELECT ... FROM translations;
SELECT ... FROM api_data JOIN translations;

Benefits

1. DRY principle: Define complex transformations once
2. Performance: Parse JSON once instead of 60+ times
3. Maintainability: Update CTE logic in one place
4. Readability: Less code repetition
5. Debugging: Easier to trace data flow

Real-World Impact

In my current project, a single page has:

• Before optimization: ~70 json_extract() calls + ~30 translation subqueries
• With SET variables: ~20 SET statements (but limited to scalars)
• With persistent CTEs: 2 CTEs, used in 15+ queries

Thank you for considering this! SQLPage is an amazing tool, and I'm happy with the current workarounds, but I wanted to open this discussion in case others face the same challenge.

---

Environment:

• SQLPage version: v0.39.1
• Database: SQLite
• Use case: Multi-component pages with complex API data parsing

[lovasoa]

Hi! It looks like what you are looking for is temporary tables ! Have a look at

https://sql-page.com/extensions-to-sql (Working with larger temporary results in particular)

https://sql-page.com/blog.sql?post=Performance%20Guide (Reusing a large query result set)

[amrutadotorg]

thank you, this will do :)