返回数据立方体的 PostgreSQL 函数 [英] PostgreSQL function returning a data cube

问题描述

首先，冰山立方体查询定义为

First off, the iceberg-cube query is defined as in

假设我有一个关系 item,location,year,supplier,unit_sales,我想写一个 plpgsql 函数作为图像中查询的包装器，用于指定参数 N，像这样:

Let's say I have a relation item,location,year,supplier,unit_sales, and I would like to write a plpgsql functions as a wrapper around the query in the image, to specify the parameter N, like so:

create or replace function iceberg_query( percentage integer ) 
returns cube
/* Code here */
as
$$
declare
    numrows int;
begin
    select count(*) into numrows from sales;
    select item, location, year, count(*) 
        from sales  
    group by cube(item,location,year)   
    having count(*) >= numrows*percentage/100;
end;
$$ language 'plpgsql'

我需要向 Code here-part 添加什么才能使其工作?如何在plpgsql中指定一个数据立方体作为返回类型?

What do I need to add to Code here-part, to make this work? How to specify a data cube as a return type in plpgsql?

推荐答案

要使您的 plpgsql 函数工作，您需要一个与您返回的内容相匹配的 RETURNS 子句.而且您实际上需要返回某些东西.我想:

To make your plpgsql function work, you need a RETURNS clause matching what you return. And you need to actually return something. I suppose:

CREATE OR REPLACE FUNCTION iceberg_query ( percentage numeric) 
  RETURNS TABLE (item ?TYPE?, location ?TYPE?, year ?TYPE?, ct bigint)
AS
$func$
DECLARE
    numrows bigint := (SELECT count(*) FROM sales);
BEGIN
   RETURN QUERY
   SELECT s.item, s.location, s.year, count(*) 
   FROM   sales s
   GROUP  BY cube(s.item,s.location,s.year)   
   HAVING count(*) >= numrows * percentage / 100;
END
$func$  LANGUAGE plpgsql;

用实际(未公开的)数据类型替换占位符 ?TYPE?.

Replace the placeholders ?TYPE? with actual (undisclosed) data types.

调用函数:

SELECT * FROM iceberg_query (10);

注意我如何对查询中的所有列名进行表限定，以避免与同名的新 OUT 参数发生命名冲突.

Note how I table-qualify all column names in the query to avoid naming collisions with the new OUT parameters of the same name.

并注意使用 numeric 而不是 integer，正如 Scoots 在 评论.

And note the use of numeric instead of integer as pointed out by Scoots in a comment.

相关:

• 如何在 PostgreSQL 中的函数内返回 SELECT 的结果?
• plpgsql 错误RETURN NEXT 不能在带有 OUT 参数的函数中包含参数"；在表返回函数中

旁白:你不需要一个函数.这个简单的 SQL 查询执行相同的操作:

Aside: you don't need a function for this. This plain SQL query does the same:

SELECT s.item, s.location, s.year, count(*)
FROM   sales s
GROUP  BY cube(s.item,s.location,s.year)
HAVING count(*) >= (SELECT count(*) * $percentage / 100 FROM sales);  -- your pct here

提供数字文字(10.0，而不是 10)以避免整数除法和随之而来的舍入.

Provide a numeric literal (10.0, not 10) to avoid integer division and the rounding that comes with it.

这篇关于返回数据立方体的 PostgreSQL 函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！