Create Function issue in the Vertica

Question

I want to create this function but facing issue

CREATE OR REPLACE FUNCTION RAW.getType(input_string VARCHAR)
RETURN VARCHAR AS
BEGIN
   RETURN (
        SELECT Type
        FROM RAW.TYPE
        WHERE LENGTH(input_string) = LENGTH
          AND input_string LIKE PATTERN || '%' 
         LIMIT 1 
     );
 END;
Gives Error as Column "input_string" does not exist

also tried with declare result

CREATE OR REPLACE FUNCTION RAW.getType(num VARCHAR)
RETURN VARCHAR
AS
BEGIN
    DECLARE result VARCHAR;

SELECT Type INTO result
FROM RAW.TYPE
WHERE LENGTH(num) = LENGTH  
  AND num LIKE PATTERN || '%'  
LIMIT 1;

RETURN result;

END;
This gives the syntax error

Need to create function query for above select query

mosheg · Answer

Vertica does not support using queries directly within User-defined SQL functions.
This is why you're encountering errors when trying to create a function with a SELECT statement inside it.
Instead of a SQL function, please consider a stored procedure (SP) approach as shown in the following example:

cat sp_test.sql
CREATE SCHEMA RAW;
create table RAW.TYPE (TYPE VARCHAR, LENGTH INT, PATTERN varchar);
insert into RAW.TYPE values ('a','1','b');
insert into RAW.TYPE values ('aa','2','bb');
insert into RAW.TYPE values ('aaa','3','bbb');
commit;

CREATE PROCEDURE RAW.getType(IN input_string VARCHAR, OUT my_result VARCHAR) AS $$
BEGIN
    my_result := SELECT Type
    FROM (
        (SELECT 1,Type FROM RAW.TYPE WHERE LENGTH(input_string) = LENGTH AND input_string LIKE PATTERN || '%' LIMIT 1)
        UNION ALL
        (SELECT 2,'No match found')
        ORDER BY 1
        LIMIT 1
    ) AS subquery;
END;
$$;

CALL RAW.getType('bb');
CALL RAW.getType('NO');
-----------------------------------
Run time output:
vsql -f sp_test.sql

 my_result
-----------
 aa
(1 row)

   my_result
----------------
 No match found
(1 row)

Sign up

Please log in or register:

Welcome to the Rocket Forum!

Please log in or register: