Geek asks and answers

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

This is the guide for posters:

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 get: Error, column "i" does not exist

What is g (i)?

+3
source share
2 answers

generate_subscripts is a set-returns function that returns multiple rows when called. This is why it most often fits in a FROM clause.

By default, the generate_subscripts results that are built into Postgres are anonymous and automatically have any name to use as a descriptor to refer to in the rest of the request. This is what g (i); this is an alias for table (g) and column (i) returned by generate_subscripts. So this expression:

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

means:

generate_subscripts "g" "i"

SQL:

CREATE TABLE g ( i integer );

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

SELECT i FROM g ORDER BY i;

SELECT , , generate_subscripts.

g (i) gs (i) Postgres. "g" "gs" "generate_series" "generate_subscripts". "i" - "". , , , , , . :

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

PostgreSQL, docs , Set Returning Functions ( 35.4.8. SQL-). , , , , .

+6

g(i) (resultset), generate_series().

g i

+1

More articles:


All Articles