Selecting

CommentsProcedural SQL (PSQL) Statements

Selecting `FROM` a stored procedure

A selectable stored procedure is a procedure that:

contains at least one output parameter, and
utilizes the SUSPEND keyword so the caller can fetch the output rows one by one, like selecting from a table or view.

The output parameters of a selectable stored procedure correspond to the columns of a regular table.

Selecting from a stored procedure without input parameters is like selecting from a table or view:

select * from suspicious_transactions
  where assignee = 'John'

Any required input parameters must be specified after the procedure name, enclosed in parentheses:

select name, az, alt from visible_stars('Brugge', current_date, '22:30')
  where alt >= 20
  order by az, alt

Values for optional parameters (that is, parameters for which default values have been defined) may be omitted or provided.However, if you provide them only partly, the parameters you omit must all be at the tail end.

Supposing that the procedure visible_stars from the previous example has two optional parameters: min_magn numeric(3,1) and spectral_class varchar(12), the following queries are all valid:

select name, az, alt
from visible_stars('Brugge', current_date, '22:30');

select name, az, alt
from visible_stars('Brugge', current_date, '22:30', 4.0);

select name, az, alt
from visible_stars('Brugge', current_date, '22:30', 4.0, 'G');

But this one isn’t, because there’s a “hole” in the parameter list:

select name, az, alt
from visible_stars('Brugge', current_date, '22:30', 'G');

An alias for a selectable stored procedure is specified after the parameter list:

select
  number,
  (select name from contestants c where c.number = gw.number)
from get_winners('#34517', 'AMS') gw

If you refer to an output parameter (“column”) by qualifying it with the full procedure name, the procedure alias should be omitted:

select
  number,
  (select name from contestants c where c.number = get_winners.number)
from get_winners('#34517', 'AMS')

Selecting `FROM` a derived table

A derived table is a valid SELECT statement enclosed in parentheses, optionally followed by a table alias and/or column aliases.The result set of the statement acts as a virtual table which the enclosing statement can query.

Syntax

(<query-expression>) [<correlation-or-recognition>]

<correlation-or-recognition> ::=
  [AS] correlation-name [(<column-name-list>)]

<column-name-list> ::= column-name [, column-name ...]

Note	The SQL standard requires the <correlation-or-recognition>, and not providing one makes it hard to reference the derived table or its columns.For maximum compatibility and portability, we recommend always specifying an alias (correlation-name).

The result set returned by this “SELECT … FROM (SELECT FROM …)” style of statement is a virtual table that can be queried within the enclosing statement, as if it were a regular table or view.

Selecting

Selecting FROM a stored procedure

Selecting FROM a derived table

Selecting `FROM` a stored procedure

Selecting `FROM` a derived table