The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

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


  • 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?

  • 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.

  • 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