FirebirdSQL logo
 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.

LATERAL Derived Tables

The keyword LATERAL marks a table as a lateral derived table.Lateral derived tables can reference tables (including other derived tables) that occur earlier in the FROM clause.See [fblangref50-dml-select-joins-lateral] for more information.

Example using a derived table

The derived table in the query below returns the list of table names in the database, and the number of columns in each table.A “drill-down” query on the derived table returns the counts of fields and the counts of tables having each field count:

SELECT
  FIELDCOUNT,
  COUNT(RELATION) AS NUM_TABLES
FROM (SELECT
        R.RDB$RELATION_NAME RELATION,
        COUNT(*) AS FIELDCOUNT
      FROM RDB$RELATIONS R
        JOIN RDB$RELATION_FIELDS RF
        ON RF.RDB$RELATION_NAME = R.RDB$RELATION_NAME
        GROUP BY RELATION)
GROUP BY FIELDCOUNT

A trivial example demonstrating how the alias of a derived table and the list of column aliases (both optional) can be used:

SELECT
  DBINFO.DESCR, DBINFO.DEF_CHARSET
FROM (SELECT *
      FROM RDB$DATABASE) DBINFO
        (DESCR, REL_ID, SEC_CLASS, DEF_CHARSET)
Note
More about Derived Tables

Derived tables can

  • be nested

  • be unions, and can be used in unions

  • contain aggregate functions, subqueries and joins

  • be used in aggregate functions, subqueries and joins

  • be calls to selectable stored procedures or queries to them

  • have WHERE, ORDER BY and GROUP BY clauses, FIRST/SKIP or ROWS directives, et al.

Furthermore,

  • Each column in a derived table must have a name.If it does not have a name, such as when it is a constant or a run-time expression, it should be given an alias, either in the regular way or by including it in the list of column aliases in the derived table’s specification.

    • The list of column aliases is optional but, if it exists, it must contain an alias for every column in the derived table

  • The optimizer can process derived tables very effectively.However, if a derived table is included in an inner join and contains a subquery, the optimizer will be unable to use any other join order.

A more useful example

Suppose we have a table COEFFS which contains the coefficients of a number of quadratic equations we have to solve.It has been defined like this:

create table coeffs (
  a double precision not null,
  b double precision not null,
  c double precision not null,
  constraint chk_a_not_zero check (a <> 0)
)

Depending on the values of a, b and c, each equation may have zero, one or two solutions.It is possible to find these solutions with a single-level query on table COEFFS, but the code will look messy and several values (like the discriminant) will have to be calculated multiple times per row.A derived table can help keep things clean here:

select
  iif (D >= 0, (-b - sqrt(D)) / denom, null) sol_1,
  iif (D >  0, (-b + sqrt(D)) / denom, null) sol_2
  from
    (select b, b*b - 4*a*c, 2*a from coeffs) (b, D, denom)

If we want to show the coefficients next to the solutions (which may not be a bad idea), we can alter the query like this:

select
  a, b, c,
  iif (D >= 0, (-b - sqrt(D)) / denom, null) sol_1,
  iif (D >  0, (-b + sqrt(D)) / denom, null) sol_2
  from
    (select a, b, c, b*b - 4*a*c as D, 2*a as denom
     from coeffs)

Notice that whereas the first query used a column aliases list for the derived table, the second adds aliases internally where needed.Both methods work, as long as every column is guaranteed to have a name.

Important

All columns in the derived table will be evaluated as many times as they are specified in the main query.This is important, as it can lead to unexpected results when using non-deterministic functions.The following shows an example of this.

SELECT
  UUID_TO_CHAR(X) AS C1,
  UUID_TO_CHAR(X) AS C2,
  UUID_TO_CHAR(X) AS C3
FROM (SELECT GEN_UUID() AS X
      FROM RDB$DATABASE) T;

The result if this query produces three different values:

C1  80AAECED-65CD-4C2F-90AB-5D548C3C7279
C2  C1214CD3-423C-406D-B5BD-95BF432ED3E3
C3  EB176C10-F754-4689-8B84-64B666381154

To ensure a single result of the GEN_UUID function, you can use the following method:

SELECT
  UUID_TO_CHAR(X) AS C1,
  UUID_TO_CHAR(X) AS C2,
  UUID_TO_CHAR(X) AS C3
FROM (SELECT GEN_UUID() AS X
      FROM RDB$DATABASE
      UNION ALL
      SELECT NULL FROM RDB$DATABASE WHERE 1 = 0) T;

This query produces a single result for all three columns:

C1  80AAECED-65CD-4C2F-90AB-5D548C3C7279
C2  80AAECED-65CD-4C2F-90AB-5D548C3C7279
C3  80AAECED-65CD-4C2F-90AB-5D548C3C7279

An alternative solution is to wrap the GEN_UUID query in a subquery:

SELECT
  UUID_TO_CHAR(X) AS C1,
  UUID_TO_CHAR(X) AS C2,
  UUID_TO_CHAR(X) AS C3
FROM (SELECT
        (SELECT GEN_UUID() FROM RDB$DATABASE) AS X
      FROM RDB$DATABASE) T;

This is an artifact of the current implementation.This behaviour may change in a future Firebird version.