support unicode literal strings prefixed with `U&`

[lovasoa]

Discussed in https://github.com/lovasoa/SQLpage/discussions/511

^{Originally posted by lozdown July 27, 2024} I have a simple Postgresql database application for managing attendance records of club members at events, with table for members, table for events and a many-to-many join table linking events and members.

This query works correctly in pgadmin4.

with 
attendances as (
 select distinct
 	em."members_Contact ID" contactid,
 	(bool_or((CASE WHEN e.eventdate ='2024-01-17' THEN true END))) "2024-01-17"
 from events_members em inner join events e on em.events_id=e.id 
 group by em."members_Contact ID"
)
select m."First Name",m."Last Name",
	case when "2024-01-17"  then U&'\2713'  else 'x' end as "2024-01-17" 

	from members m left join attendances a on m."Contact ID"=a.contactid ;

Giving example result table "F" "A" "✓" "A" "A" "✓" "C" "A" "x" "J" "F" "x"

(nb "✓" indicates attended, "x" indicates absent)

However the same query used in SQLPage throws an error associated with the escaped unicode string U&'\2173'

Error in query number 1:

Failed to execute SQL statement: WITH attendances AS (SELECT DISTINCT em."members_Contact ID" AS contactid, (bool_or((CASE WHEN e.eventdate = '2024-01-17' THEN true END))) AS "2024-01-17" FROM events_members AS em JOIN events AS e ON em.events_id = e.id GROUP BY em."members_Contact ID") SELECT m."First Name", m."Last Name", CASE WHEN "2024-01-17" THEN U & '\2713' ELSE 'x' END AS "2024-01-17" FROM members AS m LEFT JOIN attendances AS a ON m."Contact ID" = a.contactid; ⬆️ line 1, character 322

Backtrace

error returned from database: column "u" does not exist

column "u" does not exist

The SQLPage definition is

select 'table' as 'component', true as 'small', TRUE as search ;

with attendances as ( select distinct em."members_Contact ID" contactid, (bool_or((CASE WHEN e.eventdate ='2024-01-17' THEN true END))) "2024-01-17" from events_members em inner join events e on em.events_id=e.id group by em."members_Contact ID" ) select m."First Name",m."Last Name", case when "2024-01-17" then &'\2713' else 'x' end as "2024-01-17"

from members m left join attendances a on m."Contact ID"=a.contactid ;

If I change the U&'2713' to '2713' the query executes (although clearly does not display tick mark that I want)