SQLPage cannot parse sql statement using posgresql crosstab function

[lozdown]

I have a minimum page

select 'table' as component;

select * from crosstab('
	select (m."Last Name" || '', '' || m."First Name")::text , e.eventdate, ''X''::text from
	members m left join events_members em on m."Contact ID"=em."members_Contact ID"
	join events e on em.events_id = e.id 
	order by 1,2
') as atevent(
	"Last Name" text,
	"2024-06-19" text,
	"2024-09-18" text,
	"2024-03-20" text,
	"2024-01-17" text,
	"2024-04-17" text,
	"2024-05-15" text,
	"2024-02-14" text
);

The query executes correctly in pgAdmin4, but in sqlpage throws a parse error.

Parsing failed: SQLPage couldn't understand the SQL file. Please check for syntax errors:

') as atevent(
	"Last Name" text,
            ⬆️
	"2024-06-19" text,

[lovasoa]

Thank you for the report!
I reported it upstream, we will get this fixed: apache/datafusion-sqlparser-rs#1524

In the meantime, you should be able to achieve the desired result using the dynamic component and postgres built-in json functions.

See the example named Dynamic column names in https://sql.datapage.app/component.sql?component=table#component

[lovasoa]

Here is a solution using the dynamic component, that should be more easily portable across databases, and probably a little bit more performant too. It also avoids having to specify all date names in the query:

WITH filled_data AS (
  SELECT
    m."Last Name",
    m."First Name",
    e.eventdate,
    EXISTS(
        SELECT 1
        FROM events_members em
        WHERE em.events_id = e.id AND
              em."members_Contact ID" = m."Contact ID"
    ) AS participation
  FROM members m
  CROSS JOIN events e
)
SELECT 
    'dynamic' AS component,
    jsonb_build_object('Name', "Last Name" || ', ' || "First Name") ||
    jsonb_object_agg(
        eventdate,
        CASE WHEN participation THEN '✅' END
        ORDER BY eventdate
) AS properties
FROM filled_data
GROUP BY "Last Name", "First Name"
ORDER BY "Last Name", "First Name";

sqlpage_postgres_dynamic_table.zip

[lozdown]

Well I completely missed that in the documentation! That's perfect - thank you