PostgreSQL - 带有局部变量的函数 - 列引用不明确 [英] PostgreSQL - Function with local variables - Column reference is ambiguous

问题描述

我已经查看了与我的主题相匹配的其他问题，但在我的情况下，我认为歧义来自与列同名的变量.

I have had a look at other questions that match my subject but in my case the ambiguity, I believe, comes from variable having the same name as a column.

这是我尝试创建的函数的简化版本:

Here is a simplified version of the function I am trying to create:

CREATE OR REPLACE FUNCTION get_user_id(username TEXT) 
RETURNS INTEGER AS $$
  DECLARE
    user_id BIGINT;
    other_param TEXT;
  BEGIN
    SELECT INTO user_id user_id FROM users WHERE users.username = get_user_id.username;

    SELECT INTO other_param users.value FROM users WHERE users.user_id = user_id;

    RETURN user_id;
  END
$$ LANGUAGE PLPGSQL 

问题在于 WHERE 右侧的 user_id 被视为对该列的引用.

The problem is that the user_id on the right hand side of the WHERE is treated as a reference to the column.

我绝对需要在局部变量中使用 user_id，因为它将在函数中进一步的 UPDATE 和 DELETE 操作中使用，但我赢了'不把它作为参数传递，只有用户名.

I definitely need user_id in a local variable as it will be used in UPDATE and DELETE operations further along the function, but I won't get the it passed as a parameter, only username.

经过一些阅读并且之前也遇到了一些参数问题，我意识到我可以使用 get_user_id.username 但它仅适用于参数，而不适用于局部变量(如果我将它与变量一起使用，查询失败，因为 get_user_id 没有 FROM 子句).

Upon some reading and having previously had some problems with parameters too I realised I can use get_user_id.username but it only applies to parameters, not local variables (if I use it with variable the query with fail as there is not FROM-clause for get_user_id).

因此，我是否遗漏了一些(不是很明显)在函数内部查询中使用变量的内容?

Therefore am I missing something (not so) obvious about using variables in queries inside a function?

--- 编辑

抱歉我过于简化了函数，当然有第一个查询获取 user_id，这部分工作正常.

Apologies I oversimplified the function, of course there's a first query that gets the user_id, and that part is working fine.

推荐答案

我给参数和变量加上前缀，这样它们就不太可能与列名冲突:

I prefix parameters and variables so they are less likely to conflict with column names:

CREATE OR REPLACE FUNCTION get_user_id (in_username TEXT) 
RETURNS INTEGER AS $$
  DECLARE
    v_user_id BIGINT;
  BEGIN
    SELECT u.user_id  INTO v_user_id FROM users u WHERE u.user_id = in_user_id;

    RETURN v_user_id;
  END
$$ LANGUAGE PLPGSQL 

不过，大概您想比较用户名:

Presumably, though, you want to compare user names:

    SELECT u.user_id  INTO v_user_id FROM users u WHERE u.username = in_username;

这篇关于PostgreSQL - 带有局部变量的函数 - 列引用不明确的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！