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

Patrick_Hildret

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

[Deleted User]

Hi, 

You set the variable like this 

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

Hope this helps. 
Eugenia

Patrick_Hildret

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