How to using local variable in function n1ql inline

SQL++
,
1

Hi i’m new use couchbase, im using couchbase community.
This is i write in Posgresql

create function myFunction(“p_name” varchar) RETURNS TABLE (“result_1” varchar, “result_2” varchar) AS $BODY$)

declare v_var1 varchar;
declare v_var2 integer;
declare v_var3 bool;

begin

  v_var1 : = "value"; 
  v_var2 : =  1;
  v_var3 : =  true;

end;

but when i tried user defined function (UDF) in couchbase container, i get message error “Creation query: CREATE OR REPLACE FUNCTION myFunction (p_var1) LANGUAGE inline AS (declare v_var1 varchar;”…

i tried using LET, var, declare and same happen…
may i get the solution how to fix this?

regards

3

Inline UDFs are singleton expressions or statements - see:

https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/userfun.html

You are trying to write a compound statement (BEGIN … END) which isn’t supported.

If you need more complex logic then Couchbase’s solution is a Javascript UDF:

https://docs.couchbase.com/server/current/javascript-udfs/javascript-functions-with-couchbase.html

HTH.

2 Likes
4

Hi mate, thanks for your respond.

according to your answer, is there any other way but to use Javascript UDF? because i’m using community while Javascript UDF just support for Couchbase Capella and Enterprise Edition.

regards

5

Not for complex logic. If you can build it into a single expression/statement, then you can do it with an inline UDF - so it depends on what you’re looking to do.

If all you’re looking to do is return the constants "value", 1, true you can just write:

CREATE OR REPLACE FUNCTION myFunction(p_name) LANGUAGE INLINE AS 
(SELECT "value" result_1, 1 result_2, true result_3)

or

CREATE OR REPLACE FUNCTION myFunction(p_name) LANGUAGE INLINE AS
{"result_1":"value","result_2":1,"result_3":true}

Some conditional logic can be built into a statement, e.g.

CREATE OR REPLACE FUNCTION myFunction(p_name) LANGUAGE INLINE AS 
(
 SELECT CASE WHEN p_name == "" THEN "value" ELSE p_name END result_1
        ,num result_2
        ,DECODE(num,2,true,false) result_3
 FROM array_range(0,3) num
)

etc.

HTH.

1 Like
6

thanks for the example mate…

how about using the local variable in community version?
and how to write function below in couchbase sql++?

create function myFunction(“p_name” varchar) RETURNS TABLE (“result_1” varchar, “result_2” varchar) AS $BODY$)

declare v_var1 varchar;

begin

  v_var1 :=  (select code from group limit 1) ; 

  select member_name
  from member_group 
  where group_code = v_var1;

end;
7

Remember that the idea of “local variables” is a PL/SQL construct which isn’t what INLINE functions are (you are not writing a variant of PL/SQL here). Basically whatever you can construct in a direct SELECT you can place in an INLINE function, but no more - no chaining of statements, no local logic blocks etc. (Couchbase only provides the Javascript UDFs as the alternative to PL/SQL.)

Why not write that directly as:

CREATE OR REPLACE FUNCTION myFunction(p_name) LANGUAGE INLINE AS
(
  SELECT member_name
  FROM member_group
  WHERE group_code = (SELECT RAW code FROM `group` LIMIT 1)[0] -- note the array dereference and RAW modifier
)

Or alternatively:

CREATE OR REPLACE FUNCTION myFunction(p_name) LANGUAGE INLINE AS
(
  SELECT member_name
  FROM member_group
  LET v_var1 = (SELECT RAW code FROM `group` LIMIT 1)[0] -- note the array dereference and RAW modifier
  WHERE group_code = v_var1
)

?
(I personally would generally only use the LET variant if the result was used multiple times in a statement, but sometimes it lends clarity.)

Remember that the result of a SELECT statement is an array and this can be manipulated like any other array.

HTH.

Ref:
https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/selectclause.html#raw-element-value
https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/let.html

8

Of course you could simply join the two too:

CREATE OR REPLACE FUNCTION myFunction(p_name) LANGUAGE INLINE AS
(
  SELECT mg.member_name
  FROM member_group mg, (SELECT code FROM `group` LIMIT 1) g
  WHERE mg.group_code = g.code
)

If you’re looking for more than just the first group:

CREATE OR REPLACE FUNCTION myFunction(p_name) LANGUAGE INLINE AS
(
  SELECT mg.member_name
  FROM member_group mg, `group` g
  WHERE mg.group_code = g.code
)

HTH.

9

Hi mate…

thanks for your solution, time and quick response ya mate :slight_smile:

Regards

10

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.