Postgres-为什么在另一个函数中未定义的一个函数中创建了临时表？

发布于 2025-02-07 18:40:56 字数 679 浏览 1 评论 0 原文

这是一个相关的问题。

我无法弄清楚为什么我会收到错误undefined_table关系“ temp_table”不存在

我尝试了各种各样的事情，但是也许我真的不明白我如何正确使用函数中的临时表这是在另一个功能中创建的。

CREATE FUNCTION api.test() RETURNS BOOLEAN LANGUAGE 'plpgsql' SECURITY DEFINER AS $$
BEGIN
    CREATE TEMP TABLE temp_table( id INT NOT NULL) ON COMMIT DROP;
    INSERT INTO temp_table (id) VALUES (1), (2);
    RETURN TRUE;
END;
$$;

CREATE FUNCTION api.test_id(p_id INT) RETURNS BOOLEAN LANGUAGE 'plpgsql' SECURITY DEFINER AS $$
DECLARE
    v_id INTEGER;
BEGIN
    SELECT id INTO v_id FROM temp_table WHERE id = p_id LIMIT 1;
    return v_id IS NOT NULL;
END;
$$;

PS：我在函数中创建临时表，以保护其免于通过不同角色在功能外访问功能之外。

原文

this is a Postgres related question.

I can't figure out why I get ERROR undefined_table relation "temp_table" does not exist

I tried all sorts of things, but maybe I don't really understand how I correctly use a temporary table in a function that was created in another function.

CREATE FUNCTION api.test() RETURNS BOOLEAN LANGUAGE 'plpgsql' SECURITY DEFINER AS $
BEGIN
    CREATE TEMP TABLE temp_table( id INT NOT NULL) ON COMMIT DROP;
    INSERT INTO temp_table (id) VALUES (1), (2);
    RETURN TRUE;
END;
$;

CREATE FUNCTION api.test_id(p_id INT) RETURNS BOOLEAN LANGUAGE 'plpgsql' SECURITY DEFINER AS $
DECLARE
    v_id INTEGER;
BEGIN
    SELECT id INTO v_id FROM temp_table WHERE id = p_id LIMIT 1;
    return v_id IS NOT NULL;
END;
$;

PS: I create the temporary table in a function to protect it from being accessed outside of the functions by a different role.

分享到QQ

分享到微博