Open MarGraz opened 6 months ago
@MarGraz this ::
casting operator is a PostgreSQL thing, and it is not supported by grammar (and parser), all other db engines are using CAST()
function, unfortunately grammar was extended to allow us to use some functions, but currently cast(column as type)
is not supported, only calling functions like this function(column1, column2, ...)
is allowed
as a workaround, you could write your own postgresql functions and use them in your query:
[pseudocode]
@lampersky, thank you. I will try using functions and update this discussion.
Do you know if there are plans to implement the CAST()
function in the future? Because using SQL Server, I didn't have this problem (the cast was not necessary), but with PostgreSQL, I need to cast.
Thank you
Might be something of value to add a CAST() function in the generic dialect, then convert it to specific ones.
Describe the bug
I'm using a PostgreSQL database and have created a query to retrieve users stored in it. The query works when executed directly in pgAdmin, but fails when used in the Orchard Core
SQL Query
module.This is the query that works when executed on the PostgreSQL server:
This is the query used in Orchard Core's
SQL Query
module. Notice that thepublic.
prefix is omitted as it's unnecessary in Orchard Core:Below the
schema
I used:When running this query in the
Queries
>All queries
page, I receive a syntax error at line 6:Syntax error, expected: ., (, +, -, *, /, %, &, |, ^, =, >, <, >=, <=, <>, !=, !<, !>, AND, OR, LIKE, NOT LIKE, NOT, BETWEEN, IN, ,, ), GROUP, HAVING, ORDER, LIMIT, OFFSET, UNION, ;, WITH, SELECT, INNER, LEFT, RIGHT, JOIN, WHERE at line:6, col:45
After casting line 6 into text:
STRING_AGG(UBRNI."RoleName"::text, ',') AS RoleNames,
, it then indicates an error on line 5:Syntax error, expected: ., (, +, -, *, /, %, &, |, ^, =, >, <, >=, <=, <>, !=, !<, !>, AND, OR, LIKE, NOT LIKE, NOT, BETWEEN, IN, ,, ), GROUP, HAVING, ORDER, LIMIT, OFFSET, UNION, ;, WITH, SELECT, INNER, LEFT, RIGHT, JOIN, WHERE at line:5, col:31
Adding
::text
cast to line 5 leads to an error on line 4. After adding::text
also to the other rows, I went till the point where it tells me there's an error on line 1.It seems there's a bug with how Orchard Core processes the query.
To Reproduce
Steps to reproduce the behavior:
Queries
andSQL Queries
features;Expected behavior
The query that operates correctly on the PostgreSQL server should also function properly in Orchard Core's
All queries
page.