VSQL function failing on CASE


I am somewhat new to VSQL so bear with me. I am not sure why this function is failing. The issue is in the CASE statement, but I am not sure why. I set the variables and began my CASE. What am I missing?

As always, thank you all in advance for your time.

create function fn_MyFunction (AllHeaders varchar(4000)) return varchar(50)
as begin
\set subStr '"X-Forwarded-For:"'
\set strAllHeaders AllHeaders
\set p1 0
\set p2 0

when position(:subStr in :strAllHeaders ) > 0 then
\set p1 = position(:subStr IN cast(:strAllHeaders as varchar(4000)))+19

return :strAllHeaders;


Syntax error at or near "case" at character 145


  • Options
    marcothesanemarcothesane - Select Field - Administrator

    You simply cannot mix commands that go to VSQL (those beginning with a backslash) with SQL commands.

    Everything SQL goes to Vertica. Everything with a backslash is managed by vsql at the front-end. You cannot mix that.

    Can you give me an example of what the input of fn_myfunction() would look like? An example string for AllHeaders?And what your function should return?

  • Options

    Thank you for your response. The string is HUGE and has too many ID's and server names. If my company saw that string online I would get the boot :)

    In regards to\vsql commands compatibility with case statements... Is there any documentation on this? I am asking because I know they (my team) will want proof.

  • Options

    Thank you both for your responses. If I can figure out how to mark this as an answer I would.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file