开发者

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?

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜