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

Hello,

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

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

return :strAllHeaders;

end;

Syntax error at or near "case" at character 145

Comments

  • marcothesanemarcothesane 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
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.