所以我在 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 如何将变量转换为表名
发布评论