External Procedure : Alternate for Timestamp

I am trying to create an external procedure for an insert query, however, I am getting syntax error for "Timestamp" does not seem to be a proper data type that can be accepted in the parameter list of the INSERT query. Please suggest the alternative data type that can be used instead of Timestamp. Thanks, Shardul


  • Options
    Navin_CNavin_C Vertica Customer
    Hello Shardul, Can you post your insert query. What data format are you using for field Timestamp
  • Options
    Hi Navin, PFB the content of insert_query.sh: #!/usr/bin/ksh set -a vsql -h hostname -U username -w passsword -c "INSERT INTO dbname.tablename (column1, column2) values ($1, $2);" And below is how I tried creating the procedure: CREATE PROCEDURE ins_quer(col1 timestamp, col2 varchar) as 'insert_query.sh' language 'external' user 'dbadmin'; However, as mentioned earlier also, I was getting syntax error as below: "Parameter type is not valid for an external procedure: Timestamp" Please suggest! Thanks.
  • Options
    Navin_CNavin_C Vertica Customer
    Hello Shardul, Try it with DATETIME instead of Timestamp. Also The arg_type parameter is optional in CREATE PROCEDURE statement, try to give parameters without data type. Each parameter is passed to the executable file as string.
  • Options
    Thanks Navin. Will try both the options and will let you know.

Leave a Comment

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