将ResultSet传递给Postgresql函数 [英] Passing a ResultSet into a Postgresql Function

问题描述

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

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

作为一个非常人为的例子，假设我有一个查询，例如

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

SELECT id, name
FROM users
LIMIT 50

，我想创建一个函数my_function，该函数采用第一个查询的结果集并返回最小ID.在pl/pgsql中可以吗?

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?

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

推荐答案

我会反过来说，调用

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; 

这篇关于将ResultSet传递给Postgresql函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！