将列名传递给一个函数

Question

CREATE OR REPLACE FUNCTION test123(column_name VARCHAR(40)) RETURNS RECORD AS $$
DECLARE   
    rec RECORD ;
BEGIN
    SELECT * INTO rec FROM my_table WHERE column_name=1 LIMIT 1;
    RETURN rec;
END;
$$ LANGUAGE plpgsql;

你如何将列名称传递给函数，并在查询中使用该名称？

上面的代码无法正常工作：

error: operator does not exist: character varying = integer

- exebook

---

你需要使用动态SQL。请参考手册中的一些示例：https://www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN - user330315

Answer 1

当你想在函数内将函数参数作为查询中的标识符使用时，你需要动态地执行查询：

CREATE OR REPLACE FUNCTION test123(column_name VARCHAR(40)) RETURNS RECORD AS $$
DECLARE   
    rec RECORD;
BEGIN
    EXECUTE format('SELECT * FROM my_table WHERE %I = 1 LIMIT 1', column_name)
    INTO rec;
    RETURN rec;
END;
$$ LANGUAGE plpgsql;

更短一些：

CREATE OR REPLACE FUNCTION test123(column_name VARCHAR(40)) RETURNS RECORD AS $$
BEGIN
    RETURN QUERY EXECUTE format('SELECT * FROM my_table WHERE %I = 1 LIMIT 1', column_name);
END;
$$ LANGUAGE plpgsql;

- Patrick