在Postgres函数中使用参数作为列名 [英] Using parameter as column name in Postgres function

问题描述

我有一个采用以下格式的Postgres表

I have a Postgres table bearing the following form

CREATE TABLE "public"."days" 
(
 "id" integer NOT NULL,
 "day" character varying(9) NOT NULL,
 "visits" bigint[] NOT NULL,
 "passes" bigint[] NOT NULL
);

我想编写一个函数，使我可以返回 visits 或 passees 列作为指定 id 的结果.我的第一次尝试如下

I would like to write a function that allows me to return the visits or the passees column as its result for a specified id. My first attempt goes as follows

CREATE OR REPLACE FUNCTION day_entries(INT,TEXT) RETURNS BIGINT[] LANGUAGE sql AS
'SELECT $2 FROM days WHERE id = $1;'

失败，并伴随

在声明要返回bigint []的函数中返回类型不匹配详细信息:实际的返回类型是文本.

return type mismatch in function declared to return bigint[] DETAIL: Actual return type is text.

如果我放置 visits 代替 $ 2 ，事情将按预期工作.定义几个函数来匹配 days 表中的不同列几乎没有意义.有没有办法在使Postgres满意的同时传递实际的列名作为参数?

If I put in visits in place of the $2 things work just as expected. It would make little sense to define several functions to match different columns from the days table. Is there a way to pass the actual column name as a parameter while still keeping Postgres happy?

推荐答案

您不能使用参数作为标识符(=列名)，为此需要动态SQL.这需要PL/pgSQL:

You can't use parameters as identifiers (=column name), you need dynamic SQL for that. And that requires PL/pgSQL:

CREATE OR REPLACE FUNCTION day_entries(p_id int, p_column text) 
  RETURNS BIGINT[] 
AS
$$
declare 
  l_result bigint[];
begin
  execute format('SELECT %I FROM days WHERE id = $1', p_column) 
     using p_id
     into l_result;
  return l_result;
end;     
$$
LANGUAGE plpgsql;

format()正确处理构建动态SQL时使用标识符. $ 1 是一个参数占位符，它的值与 execute 语句的 using p_id 子句一起传递.

format() properly deals with identifiers when building dynamic SQL. The $1 is a parameter placeholder and the value for that is passed with the using p_id clause of the execute statement.

这篇关于在Postgres函数中使用参数作为列名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！