In PostgreSQL What is g(i) in: FROM generate_subscripts($1, 1) g(i)?

Question

This is in the postgres manual:

CREATE or replace FUNCTION mleast(a VARIADIC numeric[]) 
   RETURNS numeric 
AS $$
   SELECT min($1[i]) FROM generate_subscripts($1, 1) g(i);
$$ LANGUAGE SQL;

SELECT mleast(10, -1, 5, 4.4);

if I write: ( ommiting g(i) )

CREATE or replace FUNCTION mleast(a VARIADIC numeric[]) 
   RETURNS numeric 
AS $$
  SELECT min($1[i]) FROM generate_subscripts($1, 1);
$$ LANGUAGE SQL;


SELECT mleast(10, -1, 5, 4.4);

I receive: Error does not exist the column «i»

What exactly is g(i)?

Answer 1

generate_subscripts is a "set-returning function", which will return multiple rows when you call it. That's why it's most often put in the FROM clause.

By default, the results from generate_subscripts, which comes built-in with Postgres is anonymous and does automatically have any name to use as a handle in order to refer to it in the rest of the query. This is what the g(i) is; it's an alias for the table (g) and the column (i) returned by generate_subscripts. So this expression:

FROM generate_subscripts($1, 1) g(i)

means:

execute the function generate_subscripts and assign its results to a table called "g" with a single column called "i"

or in SQL form:

CREATE TABLE g ( i integer );

INSERT INTO g SELECT * FROM generate_subscripts(some_array, 1);

SELECT i FROM g ORDER BY i;

Without that alias, the SELECT portion of your query has no idea how to reference the results produced by generate_subscripts.

The use of g(i) and gs(i) is a convention in the Postgres world. "g" or "gs" stands for "generate_series" or "generate_subscripts". "i" is the traditional programming variable for "iterator". You can actually use any aliases you want, and I encourage you to use aliases which actually reference what you're trying to do, in order to improve future code maintenance. For example:

FROM generate_subscripts( $1, 1 ) as features(feature_no)

These functions are detailed in the PostgreSQL docs, and it's also useful to reference the docs on how to write Set Returning Functions (scroll down to 35.4.8. SQL Functions Returning Sets). Once you write one yourself, it becomes clearer why you need an alias to reference its results.

Answer 2

g(i) defines the structure of the table (resultset) that is returned by the generate_series() function.

It assigns the alias g to the resultset with a single column named i