PostgreSQL function returning a data cube

Question

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

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'

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?

The code goes between the two dollar signs $$, you need to finish the code already written. No code should be written where that comment lies. — Lucas, Mar 19 '19 at 12:03
Please never post text or code as image. One cannot copy/paste from an image. A table definition (`CREATE TABLE` statement as text!) and your version of Postgres would be helpful, too. — Erwin Brandstetter, Mar 19 '19 at 12:59
@Lucas: True, the function body *"goes between the two dollar signs"*. But the wrapping `CREATE FUNCTION` statement is *code*, too (SQL DDL code vs. plpgsql). Improvements in both departments were needed. — Erwin Brandstetter, Mar 19 '19 at 13:24

Erwin Brandstetter · Accepted Answer · 2019-03-19T13:09:42.227

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;

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

Call the function with:

SELECT * FROM iceberg_query (10);

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

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

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

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

I think you need to typecast the percentage in your query to type numeric. An integer divided by any number will always yield an integer. — Scoots, Mar 19 '19 at 13:04

PostgreSQL function returning a data cube

1 Answers1

Linked