Options

Create Function issue in the Vertica

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

Answers

  • Options
    moshegmosheg Vertica Employee Administrator

    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)
    
  • Options

    But procedure wont return value So I cant use it my Select query as functions?

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file