VSQL function failing on CASE

Slaagh

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

marcothesane

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?

Slaagh

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.

Jim_Knicely

Meta commands are vsql specific...

https://my.vertica.com/docs/9.0.x/HTML/index.htm#Authoring/ConnectingToVertica/vsql/Meta-Commands.htm

Slaagh

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