将ResultSet传递给Postgresql函数

Is it possible to pass the results of a postgres query as an input into another function?

是否可以将postgres查询的结果作为输入传递给另一个函数?

As a very contrived example, say I have one query like

作为一个非常人为的例子,说我有一个像

SELECT id, name
FROM users
LIMIT 50

and I want to create a function my_function that takes the resultset of the first query and returns the minimum id. Is this possible in pl/pgsql?

我想创建一个函数my_function,它接受第一个查询的结果集并返回最小id。这可能在pl / pgsql中吗?

SELECT my_function(SELECT id, name FROM Users LIMIT 50); --returns 50

4 个解决方案

#1

It is not possible to pass an array of generic type RECORD to a plpgsql function which is essentially what you are trying to do.

无法将通用类型RECORD数组传递给plpgsql函数,而这正是您尝试执行的操作。

What you can do is pass in an array of a specific user defined TYPE or of a particular table row type. In the example below you could also swap out the argument data type for the table name users[] (though this would obviously mean getting all data in the users table row).

您可以做的是传入特定用户定义的TYPE或特定表行类型的数组。在下面的示例中,您还可以替换表名称users []的参数数据类型(尽管这显然意味着获取users表行中的所有数据)。

CREATE TYPE trivial {
"ID" integer,
"NAME" text
}

CREATE OR REPLACE FUNCTION trivial_func(data trivial[])
  RETURNS integer AS
$BODY$
DECLARE 

BEGIN
    --Implementation here using data
    return 1;

END$BODY$
  LANGUAGE 'plpgsql' VOLATILE;

#2

I would take the problem on the other side, calling an aggregate function for each record of the result set. It's not as flexible but can gives you an hint to work on.

我会在另一方面解决问题,为结果集的每个记录调用一个聚合函数。它不是那么灵活,但可以提供一些工作提示。

As an exemple to follow your sample problem:

作为示例来跟踪您的示例问题:

CREATE OR REPLACE FUNCTION myMin ( int,int ) RETURNS int AS $$
  SELECT CASE WHEN $1 < $2 THEN $1 ELSE $2 END;
$$ LANGUAGE SQL STRICT IMMUTABLE;

CREATE AGGREGATE my_function ( int ) (
    SFUNC = myMin, STYPE = int, INITCOND = 2147483647 --maxint
);

SELECT my_function(id) from (SELECT * FROM Users LIMIT 50) x; 

#3

You could use a cursor, but that very impractical for computing a minimum.

您可以使用游标,但这对于计算最小值非常不切实际。

I would use a temporary table for that purpose, and pass the table name for use in dynamic SQL:

我会为此目的使用临时表,并传递表名以在动态SQL中使用:

CREATE OR REPLACE FUNCTION f_min_id(_tbl regclass, OUT min_id int) AS 
$func$
BEGIN

EXECUTE 'SELECT min(id) FROM ' || _tbl
INTO min_id;

END  
$func$ LANGUAGE plpgsql;

Call:

CREATE TEMP TABLE foo ON COMMIT DROP AS
SELECT id, name
FROM   users
LIMIT  50;

SELECT f_min_id('foo');

Major points

• The first parameter is of type regclass to prevent SQL injection. More info in this related answer on dba.SE.

  第一个参数是regclass类型,以防止SQL注入。有关dba.SE的相关答案的更多信息。

• I made the temp table ON COMMIT DROP to limit its lifetime to the current transaction. May or may not be what you want.

  我将临时表设置为ON COMMIT DROP以将其生命周期限制为当前事务。可能或不是你想要的。

• You can extend this example to take more parameters. Search for code examples for dynamic SQL with EXECUTE.

  您可以扩展此示例以获取更多参数。使用EXECUTE搜索动态SQL的代码示例。

-> SQLfiddle demo

- > SQLfiddle演示

#4

I think there's no way to pass recordset or table into function (but I'd be glad if i'm wrong). Best I could suggest is to pass array:

我认为没有办法将记录集或表传递给函数(但如果我错了,我会很高兴)。我建议的最好是传递数组:

create or replace function my_function(data int[])
returns int
as
$$
   select min(x) from unnest(data) as x
$$
language SQL;

sql fiddle demo

sql小提琴演示