表作为PostgreSQL函数的参数

问题描述

我正在尝试将表作为PostgreSQL函数中的参数传递，但在第一行中找不到正确的关键字(在我的代码中应使用代替 table 的关键字):

I'm trying to pass a table as an argument in a PostgreSQL function, and I can't find the right keyword (what should be instead of table in my code) in the first line :

CREATE FUNCTION test.fcttest(table)   
RETURNS SETOF test.out_table
AS $$
    INSERT INTO test.out_table /* obviously, the schema for this table is what it should be, an int column for A.id and a varchar(30) column for A.another_field */
        SELECT A.id, A.another_field
        FROM $1 A;
    SELECT A.id, A.another_field 
        FROM $1 A;
$$ 
LANGUAGE SQL;

或者也许它不能像这样工作?如果是这样，我该怎么办?

Or maybe it just can't work like this ? If so, how should I do it ?

(此外，我想我的函数中可能还会存在与SQL注入相关的问题，但请暂时忽略它们，这是针对安全环境中的原型的，因此我暂时不必担心.如果您确实解决了此问题，请在答案的另一部分中这样做，以便我能理解是什么意思:)).

(Also, I guess there might also be SQL injection related issues with my function, but please ignore them for now, it's for a prototype in a secure environment so I don't have to worry about it for the moment. Or if you do address it, please do so in a separate part of your answer so I can understand what adresses what :) ).

谢谢.

推荐答案

您不能传递诸如参数之类的表，而只能传递表名:

You can not pass a table as such as a parameter, only the name of the table:

CREATE FUNCTION test.fcttest(tbl text) RETURNS SETOF test.out_table AS $$
BEGIN
  EXECUTE format('INSERT INTO test.out_table '
                   'SELECT id, another_field FROM %I', tbl);
  RETURN QUERY EXECUTE format('SELECT id, another_field FROM %I', tbl);
END;
$$ LANGUAGE plpgsql;

传递表名时，您需要执行动态命令.您只能在plpgsql函数中执行此操作. format()函数和％I 修饰符可以防止SQL注入.使用 返回行再次使用动态命令返回下一个查询 短语.

When passing a table name you need to execute a dynamic command. You can only do this in a plpgsql function. The use of the format() function with the %I modifier protects against SQL injection. The rows are returned using the RETURN NEXT QUERY phrase, again with a dynamic command.

请注意，此逻辑两者都将一组记录插入表 test.out_table 中，然后返回相同的记录集.不确定这是否是您真正想要的.

Note that this logic both inserts a set of records into table test.out_table and then returns the same set of records. Not sure if that is what you really want.

这篇关于表作为PostgreSQL函数的参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！