PL/PostgreSQL 如何将变量转换为表名 [英] PL/PostgreSQL how to convert a variable into a table name

问题描述

所以我在 PostgreSQL 中有一个函数可以从动态表中动态选择列.我从 这篇文章，除了一件事之外，它的作用还不错.

So I have a function in PostgreSQL that dynamically selects columns from a dynamic table. I got this solution from this post and it works great other than one thing.

这是连接到节点服务器的文件内部，因此第二个 SELECT * FROM 中的 $1 和 $2 代表从那里传递的值.现在的问题是我收到了一个我不明白的语法错误(我对 SQL 比较陌生，所以这可能是原因).

This is inside of a file that is connected to a Node server, and so the $1 and $2 in the second SELECT * FROM represent values passed from there. The issue right now is that I am getting a syntax error that I don't understand (I am newer to SQL so that may be why).

$2 表示要从字符串中选择的表的名称，例如它可以是目标".该错误是目标"处或附近的语法错误.我意识到它不能是带单引号的字符串(我相信)，所以我想知道如何将该变量转换为表名?使用目标"那里以及目标，例如按预期工作，但我不确定如何在函数之外做到这一点.

$2 represents the name of the table to be selected from as a string, so for example it could be 'goals'. The error is syntax error at or near "'goals'". I realize that it cannot be a string with single quotes (I believe) and so I am wondering how to convert that variable to be a table name? using "goals" there as well as goals, for example works as expected, though I'm not sure how to do that outside of a function.

CREATE OR REPLACE FUNCTION get_data(user_id INT, table_name anyelement)
RETURNS SETOF ANYELEMENT AS $$
    BEGIN
        RETURN QUERY EXECUTE 
            format('SELECT * FROM %s WHERE user_id = $1', pg_typeof(table_name)) USING user_id;
    END;
$$ LANGUAGE plpgsql;

SELECT * FROM get_data($1, NULL::$2);

例如，$1 是 5，$2 是目标"

$1 is 5 and $2 is 'goals' for example

推荐答案

经过几个小时的尝试，多亏了 Adrian 的评论，我发现 MassiveJS(我如何连接到我的 PostgreSQL 服务器)有 内联函数进行查询.在我的服务器中的控制器文件中，我能够创建一个这样的单行函数:

After many hours of trying to figure it out, thanks to Adrian's comment, I found MassiveJS (how I'm connecting to my PostgreSQL server) has inline functions to do queries. In my controller file in my server I was able to create a one line function as such:

const data = await db[tableName].where("user_id=$1", [userId])

不知道 MassiveJS 中存在内联 SQL，所以发现它很棒！

Didn't know inline SQL existed in MassiveJS, so that was great to find out!

这篇关于PL/PostgreSQL 如何将变量转换为表名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！