postgresql 函数错误：列名不存在
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17467114/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
postgresql function error: column name does not exist
提问by giozh
i've implemented a function that check if a value appears in a specific row of a specific table:
我已经实现了一个函数来检查一个值是否出现在特定表的特定行中：
CREATE FUNCTION check_if_if_exist(id INTEGER, table_name character(50), table_column character(20) ) RETURNS BOOLEAN AS $$
DECLARE res BOOLEAN;
BEGIN
SELECT table_column INTO res
FROM table_name
WHERE table_column = id;
RETURN res;
END;
$$ LANGUAGE plpgsql
i've create and fill a simple test table for try this function:
我已经创建并填充了一个简单的测试表来尝试这个功能：
CREATE TABLE tab(f INTEGER);
and i call function like
我调用函数
SELECT check_if_exist(10, tab, f);
but i occurs in this error:
但我发生在这个错误中：
ERROR: column "prova" does not exist
LINE 1: SELECT check_if_exist(10, tab, f);
^
********** Error **********
ERROR: column "tab" does not exist
SQL state: 42703
Character: 27
why?
为什么？
采纳答案by ElmoVanKielmo
Your code has no chance to work - when dealing with different tables in PLPGSQL you need to utilize dynamic queries, so EXECUTEis required - http://www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
But first of all - there is nothing bad in using PostgreSQL EXISTS- http://www.postgresql.org/docs/current/static/functions-subquery.html#AEN15284instead of inventing your own - performance of your solution will be significantly worse than using included batteries...
Hopefully this is helpful. Good luck.
您的代码没有机会工作 - 在 PLPGSQL 中处理不同的表时，您需要利用动态查询，因此EXECUTE需要 - http://www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL- STATEMENTS-EXECUTING-DYN
但首先 - 使用 PostgreSQL 没有什么不好EXISTS- http://www.postgresql.org/docs/current/static/functions-subquery.html#AEN15284而不是发明你自己的 - 你的性能解决方案将比使用随附的电池要糟糕得多......
希望这会有所帮助。祝你好运。
回答by Micha? Niklas
In addition to Elmo response you must be careful with types. You have got:
除了 Elmo 响应之外，您还必须小心类型。你必须：
ERROR: column "tab" does not exist
because SQL parser do not know how to deal with tabwhich is without quote. Your query must be like:
因为SQL解析器不知道如何处理tab没有引号的。您的查询必须是这样的：
SELECT check_if_exist(10, 'tab', 'f');
As Elmo answered you use dynamic query, so even if you quote tabyou will got error:
正如 Elmo 回答你使用动态查询，所以即使你引用tab你会得到错误：
ERROR: relation "table_name" does not exist
so you can use EXECUTE, example:
所以你可以使用EXECUTE，例如：
CREATE OR REPLACE FUNCTION check_if_exist(id INTEGER, table_name varchar, table_column varchar) RETURNS BOOLEAN AS $$
DECLARE
sql varchar;
cnt int;
BEGIN
sql := 'SELECT count(*) FROM ' || quote_ident(table_name) || ' WHERE ' || quote_ident(table_column) || '=';
RAISE NOTICE 'sql %', sql;
EXECUTE sql USING id INTO cnt;
RETURN cnt > 0;
END;
$$ LANGUAGE plpgsql
You can also use VARCHARinstead of character(N)in function arguments and use CREATE OR REPLACE FUNCTION ...instead of just CREATE FUNCTION ...which is very handy at debugging.
您还可以使用VARCHAR，而不是character(N)在函数参数和使用CREATE OR REPLACE FUNCTION ...，而不是仅仅CREATE FUNCTION ...是在调试非常方便。
