Determining the column name passed to a Pg function
I have a PL/pgsql function like so
CREATE OR REPLACE FUNCTION foo(colname TEXT, col INT)
RETURNS REAL AS $$
BEGIN
IF (colname = 'a') THEN
RETURN (col * 1.5);
ELSIF (colname = 'b') THEN
RETURN (col * 2.5);
ELSIF (colname = 'c') THEN
RETURN (col * 3.5);
.. and so on ..
ELSE
RAISE EXCEPTION 'Invalid column!';
END IF;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;
The function allows me to do stuff like
SELECT foo('a', a) FROM table WHERE
I would like to make this nicer, and not have to pass the column name, if I can help it. In other words, I would like to be able to do
SELECT foo(a) FROM table WHERE
and figure out the column name in the func开发者_如何学Ction based on the col passed to the function. Is that possible?
No. What's passed to a function is just a value that is the result of an expression. It makes no difference the your expression happens to be a single column name. What if you were to write the query like this?
SELECT foo(a+2) FROM table WHERE ...
Or this?
SELECT foo(2+2) FROM table WHERE ...
What should the function infer is the column name in these cases?
精彩评论