Postgres pl/pgsql错误:列"column_name"不存在 [英] Postgres pl/pgsql ERROR: column "column_name" does not exist

问题描述

我有一个存储过程，如下所示，

i have a storerd procedure like below,

CREATE FUNCTION select_transactions3(text, text, int)    
RETURNS SETOF transactions AS   
$body$   
DECLARE    
    rec transactions%ROWTYPE;  
BEGIN
    FOR rec IN (SELECT invoice_no, trans_date FROM transactions WHERE $1 = $2 limit $3  )    
    LOOP     
        RETURN NEXT rec;    
    END LOOP;  
END;   
$body$  
LANGUAGE plpgsql VOLATILE SECURITY DEFINER;

当我执行这样的查询时:

when i execute query like this :

select * from select_transactions3("invoice_no", '1103300105472',10);

或

select * from select_transactions3(invoice_no, '1103300105472',10);

它得到这样的错误: 错误:列"invoice_no"不存在

it getting error like this : ERROR: column "invoice_no" does not exist

但是当我尝试用这样的一个冒号执行时:

but when i try execute with one colon like this :

select * from select_transactions3('invoice_no', '1103300105472',10);

结果是没有行.

我如何获取这样的数据:

how i can get the data like this :

  invoice_no   |       trans_date        
---------------+-------------------------
 1103300105472 | 2011-03-30 12:25:35.694

谢谢.

UPDATE:如果我们想要显示表的某一列

UPDATE : If we want a certain column of table that we want to show

CREATE FUNCTION select_to_transactions14(_col character varying, _val character varying, _limit int) 
RETURNS SETOF RECORD AS
$$
DECLARE
 rec record;
BEGIN
 FOR rec IN EXECUTE 'SELECT invoice_no, amount FROM transactions
                 WHERE  ' || _col || ' = $1 LIMIT $2' USING _val, _limit            LOOP
  RETURN NEXT rec;
 END LOOP;
END;
$$ LANGUAGE plpgsql;

获得结果:

SELECT * FROM select_to_transactions14( 'invoice_no', '1103300105472',1)
as ("invoice_no" varchar(125), "amount" numeric(12,2));

推荐答案

您的函数可能如下所示:

Your function could look like this:

CREATE FUNCTION select_transactions3(_col text, _val text, _limit int)    
  RETURNS SETOF transactions AS   
$BODY$   
BEGIN

RETURN QUERY EXECUTE '
   SELECT *
   FROM   transactions
   WHERE  ' || quote_ident(_col) || ' = $1
   LIMIT  $2'
USING _val, _limit;

END;   
$BODY$  
LANGUAGE plpgsql VOLATILE SECURITY DEFINER;

在PostgreSQL 9.1 或更高版本中，使用

IN PostgreSQL 9.1 or later that's simpler with format()

...
RETURN QUERY EXECUTE format('
   SELECT *
   FROM   transactions
   WHERE  %I = $1
   LIMIT  $2', _col)
USING _val, _limit;
...

%I转义quote_ident()之类的标识符.

• 您遇到了动态SQL的局限性，即不能使用参数作为标识符.您必须使用列名称构建查询字符串，然后然后执行它.

您可以使用值来做到这一点.我演示了EXECUTE的USING子句的用法.还要注意使用 quote_ident() :防止SQL注入和某些语法错误.

You can do that with values though. I demonstrate the use of the USING clause for EXECUTE. Also note the use of quote_ident(): prevents SQL injection and certain syntax errors.

我还大大简化了您的功能. [RETURN QUERY EXECUTE][3]使您的代码更短，更快.如果您要做的就是循环返回行，则无需循环.

I also largely simplified your function. [RETURN QUERY EXECUTE][3] makes your code shorter and faster. No need to loop if all you do is return the row.

我使用命名为IN的参数，因此您不会与查询字符串中的$表示混淆.查询字符串中的$1和$2引用USING子句中提供的值，而不是输入参数.

I use named IN parameters, so you don't get confused with the $-notation in the query string. $1 and $2 inside the query string refer to the values provided in the USING clause, not to the input parameters.

我更改为SELECT *，因为无论如何您必须返回整行以匹配声明的返回类型.

I change to SELECT * as you have to return the whole row to match the declared return type anyway.

最后但并非最不重要的一点:一定要考虑手册对于声明的

Last but not least: Be sure to consider what the manual has to say about functions declared SECURITY DEFINER.

如果您不想返回整行，则一种方便的可能性是:

If you don't want to return the whole row, one convenient possibility is:

CREATE FUNCTION select_transactions3(_col text, _val text, _limit int)    
  RETURNS TABLE (invoice_no varchar(125), amount numeric(12,2) AS ...

然后，您不必在每次调用时都提供列定义列表，并且可以简化为:

Then you don't have to provide a column definition list with every call and can simplify to:

SELECT * FROM select_to_transactions3('invoice_no', '1103300105472', 1);

这篇关于Postgres pl/pgsql错误:列"column_name"不存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！