Error handling

[Aesth]

I have a function on the database server that, when called, returns a value, based on which various actions need to be performed. As far as I understand, SQLPage is practically the only way to handle conditions using the redirect component. But so far, all my attempts at implementing it have proven clumsy.
I don't want to create multiple files for every possible function result. Passing the result via the redirect page variable is also a bad idea, since depending on the result, different pages may need to be opened, which could require complex SELECT statements. What would be the best way to implement this functionality?

[Aesth]

I wanted to do this.

SET result = (SELECT public.p(:u_parm::character varying));

SELECT
    'redirect' AS component,
    'oops?result='||result AS link
WHERE NOT result = 'OK'
;

SELECT 
    'alert' as component
    , 'Ok !' as title
    , 'check' as icon
    , 'green' as color
;

But I'm getting an error with the redirect component in the WHERE NOT clause, because the result column is missing. So, the variable result is retrieved, but I can't use it either like that or with any of the prefixes (: or $). What am I doing wrong?
Second question: is it possible to use post instead of get for the redirect component, so the user doesn't see that I'm passing a parameter.

[Aesth]

Oops ! Again, inattention, the error was not in the where clause, but in the link concatenation, and it was correct like this
SELECT 'redirect' AS component, 'oops?result='||$result AS link WHERE NOT $result = 'OK' ;

[lovasoa]

Indeed, your solution seems good:

SET result = public.p(:u_parm::text);

SELECT
    'redirect' AS component,
    'oops?result='||$result AS link
WHERE $result <> 'OK';

SELECT 
    'alert' as component
    , 'Ok !' as title
    , 'check' as icon
    , 'green' as color
;

What does public.p do, and what feature are you implementing ? I may be able to give better advice with more context.

[Aesth]

I'm currently trying to gather standard solutions for various situations to switch to a new approach.
What about hidden parameter passing in oops.sql in redirect? Is that possible? And one more thing. What about an assignment option other than SET, a construct similar to SELECT in MS SQL, like
SELECT TOP 1 result1 = public.f1(:u_parm::text), result2 = public.f2() [ , result3 = field1, result4 = field2 FROM table1];

[lovasoa]

What about hidden parameter passing in oops.sql in redirect? Is that possible?

No, browser redirections do not support POST. That's a good thing in general and a better user experience to keep state either in the url or in the database. You can record errors in the database and redirect to a page with just an id in the url.

What about an assignment option other than SET, a construct similar to SELECT in MS SQL

SET is the only way to set a variable. See https://sql-page.com/extensions-to-sql about how to store more complex types.

[Aesth]

Regarding the redirect component, I understand. But if I understand correctly, can I pass the parameter via cookies? I think passing it via the database is inappropriate, at least because of its bloat. It requires at least three roundtrips to the database server: saving, reading, and deleting the parameter.

Regarding store more complex types, is this about Small JSON values ​​in variables ?

set product = (
    select json_object('name', name, 'price', price)
    from products where id = $product_id
);

I've read about it, but it seems to me that storing complex data as strings leads to a performance loss due to the complexity of modification and the need for parsing to obtain a specific value. I wanted something simpler, but if there's no other option, then fine.

Regarding my code example, in theory it's even simpler. I was pleased that you can call the function without the required SELECT before it; it looks more elegant. And my code should have been even simpler:

SET result = public.p(:u_parm::character varying);

SELECT
    'redirect' AS component,
    'Wow' AS link
WHERE $result = 'OK'
;

SELECT 
    'alert' AS component
    , 'Error !' AS title
    'alert-circle' AS icon,
    'red' AS color,
    (CASE $result
        WHEN '1' THEN 'First !'
        WHEN '2' THEN 'Second !'
        ELSE 'Third!'
    END) AS description
;

P.S. I understand that this is more difficult to implement, but I think it would be great if some simple calculated expressions, which generally don't require a database call and operate only on SQLPage variables, were calculated by SQLPage itself. And the library of internal functions would also be expanded. But this is just a fantasy; in reality, SQLPage is already very good, in my opinion.

P.P.S. By the way, forgive me for another digression, but maybe we should make the connection to the local SQLite unconditional and use it as an additional engine? And with the other database servers, leave everything as usual, with just one working connection.

[lovasoa]

Regarding the redirect component, I understand. But if I understand correctly, can I pass the parameter via cookies? I think passing it via the database is inappropriate, at least because of its bloat. It requires at least three roundtrips to the database server: saving, reading, and deleting the parameter.

I was not thinking of deleting it. I was thinking of storing the results in the database permanently. If consulting the errors afterwards is never useful, then it's indeed overkill.

I've read about it, but it seems to me that storing complex data as strings leads to a performance loss due to the complexity of modification and the need for parsing to obtain a specific value.

I have never seen it be a performance issue. It's in general not a limiting factor compared to the queries themselves. If you hit a case where it is, I'm interested to hear about it !

I understand that this is more difficult to implement, but I think it would be great if some simple calculated expressions, which generally don't require a database call and operate only on SQLPage variables, were calculated by SQLPage itself. And the library of internal functions would also be expanded.

The infrastructure for this is already in place. Currently the only expressions that sqlpage can evaluate on its own (only when they are passed as arguments to sqlpage functions) are concat, coalesce, json_object, json_array. Contributions to src/webserver/database/syntax_tree.rs are welcome. However, they require careful testing for cross-database compatibility. A lot of very basic sql constructs have subtle semantics differences between databases, and SQLPage tries to avoid evaluating expressions differently from how your database would do it.