We're Moving!

The Vertica Forum is moving to a new OpenText Analytics Database (Vertica) Community.

Join us there to post discussion topics, learn about

product releases, share tips, access the blog, and much more.

Create My New Community Account Now


How can I pass a variable to a sql statement inside a function — Vertica Forum

How can I pass a variable to a sql statement inside a function

I would like to create a function that returns the rowcount from a table based on the value I pass in to the function. Like this: CREATE OR REPLACE FUNCTION get_my_rowcount(tblname VARCHAR(50),id INT) RETURN INT AS BEGIN RETURN ( CASE WHEN tblname = 'table1' THEN (SELECT COUNT(*) FROM table1 WHERE my_id = id) WHEN tblname = 'table2' THEN (SELECT COUNT(*) FROM table2 WHERE my_id = id) WHEN tblname = 'table3' THEN (SELECT COUNT (*) FROM table3 WHERE my_id = id) ELSE 0 END ); END; When I run this I get the following error: ROLLBACK: column "id" does not exist

Comments

  • Hi, 

    You set the variable like this 

    \set id 123123
    select * from test where id = :id;

    Hope this helps. 
    Eugenia
  • Hi Eugenia, Thanks for the quick reply! I know that's how that works in the context of a sql script but, how do I use that in the context of the function above? When i use the :id in my function I get this error: ERROR: syntax error at or near ":" at character 218 LINE 4: ...T COUNT(*) FROM table1 WHERE my_id = :id) And when I add /set myid id to the function, I get: ERROR: syntax error at or near "/" at character 94 LINE 3: /set myid id

Leave a Comment

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