The ORDER BY
Clause
The ORDER BY
can be used to influence the order in which rows are evaluated.The primary use case is when combined with RETURNING
, to influence the order rows are returned.
Examples of SELECT queries with different types of column lists
ORDER BY
ClauseThe ORDER BY
can be used to influence the order in which rows are evaluated.The primary use case is when combined with RETURNING
, to influence the order rows are returned.
RETURNING
ClauseA MERGE
statement can contain a RETURNING
clause to return rows added, modified or removed.The merge is executed to completion before rows are returned.The RETURNING
clause can contain any columns from the target table (or updatable view), as well as other columns (eg from the source) and expressions.
The user executing the statement needs to have SELECT
privileges on the columns specified in the RETURNING
clause.
In PSQL, If a RETURNING
clause is present and more than one matching record is found, an error “multiple rows in singleton select” is raised.This behaviour may change in a future Firebird version.
The optional INTO
sub-clause is only valid in PSQL.
Column names can be qualified by the OLD
or NEW
prefix to define exactly what value to return: before or after modification. The returned values include the changes made by BEFORE
triggers.
The syntax of the returning_list is similar to the column list of a SELECT
clause.It is possible to reference all columns using *
, or table_name.*
, NEW.*
and/or OLD.*
.
For the UPDATE
or INSERT
action, unqualified column names, or those qualified by the target table name or alias will behave as if qualified by NEW
, while for the DELETE
action as if qualified by OLD
.
The following example modifies the previous example to affect one line, and adds a RETURNING
clause to return the old and new quantity of goods, and the difference between those values.
MERGE
with a RETURNING
clauseMERGE INTO PRODUCT_INVENTORY AS TARGET
USING (
SELECT
SL.ID_PRODUCT,
SUM(SL.QUANTITY)
FROM SALES_ORDER_LINE SL
JOIN SALES_ORDER S ON S.ID = SL.ID_SALES_ORDER
WHERE S.BYDATE = CURRENT_DATE
AND SL.ID_PRODUCT =: ID_PRODUCT
GROUP BY 1
) AS SRC (ID_PRODUCT, QUANTITY)
ON TARGET.ID_PRODUCT = SRC.ID_PRODUCT
WHEN MATCHED AND TARGET.QUANTITY - SRC.QUANTITY <= 0 THEN
DELETE
WHEN MATCHED THEN
UPDATE SET
TARGET.QUANTITY = TARGET.QUANTITY - SRC.QUANTITY,
TARGET.BYDATE = CURRENT_DATE
RETURNING OLD.QUANTITY, NEW.QUANTITY, SRC.QUANTITY
INTO : OLD_QUANTITY, :NEW_QUANTITY, :DIFF_QUANTITY
MERGE
Update books when present, or add new record if absent
MERGE INTO books b
USING purchases p
ON p.title = b.title and p.type = 'bk'
WHEN MATCHED THEN
UPDATE SET b.desc = b.desc || '; ' || p.desc
WHEN NOT MATCHED THEN
INSERT (title, desc, bought) values (p.title, p.desc, p.bought);
Using a derived table
MERGE INTO customers c
USING (SELECT * from customers_delta WHERE id > 10) cd
ON (c.id = cd.id)
WHEN MATCHED THEN
UPDATE SET name = cd.name
WHEN NOT MATCHED THEN
INSERT (id, name) values (cd.id, cd.name);
Together with a recursive CTE
MERGE INTO numbers
USING (
WITH RECURSIVE r(n) AS (
SELECT 1 FROM rdb$database
UNION ALL
SELECT n+1 FROM r WHERE n < 200
)
SELECT n FROM r
) t
ON numbers.num = t.n
WHEN NOT MATCHED THEN
INSERT(num) VALUES(t.n);
Using DELETE
clause
MERGE INTO SALARY_HISTORY
USING (
SELECT EMP_NO
FROM EMPLOYEE
WHERE DEPT_NO = 120) EMP
ON SALARY_HISTORY.EMP_NO = EMP.EMP_NO
WHEN MATCHED THEN DELETE
The following example updates the PRODUCT_INVENTORY
table daily based on orders processed in the SALES_ORDER_LINE
table.If the stock level of the product would drop to zero or lower, then the row for that product is removed from the PRODUCT_INVENTORY
table.
MERGE INTO PRODUCT_INVENTORY AS TARGET
USING (
SELECT
SL.ID_PRODUCT,
SUM (SL.QUANTITY)
FROM SALES_ORDER_LINE SL
JOIN SALES_ORDER S ON S.ID = SL.ID_SALES_ORDER
WHERE S.BYDATE = CURRENT_DATE
GROUP BY 1
) AS SRC (ID_PRODUCT, QUANTITY)
ON TARGET.ID_PRODUCT = SRC.ID_PRODUCT
WHEN MATCHED AND TARGET.QUANTITY - SRC.QUANTITY <= 0 THEN
DELETE
WHEN MATCHED THEN
UPDATE SET
TARGET.QUANTITY = TARGET.QUANTITY - SRC.QUANTITY,
TARGET.BYDATE = CURRENT_DATE
EXECUTE PROCEDURE
Executes a stored procedure
EXECUTE PROCEDURE procname [{ <inparam-list | ( <inparam-list> ) }] [RETURNING_VALUES { <outvar-list> | ( <outvar-list ) }] <inparam-list> ::= <inparam> [, <inparam> ...] <outvar-list> ::= <outvar> [, <outvar> ...] <outvar> ::= [:]varname
Argument | Description |
---|---|
procname |
Name of the stored procedure |
inparam |
An expression evaluating to the declared data type of an input parameter |
varname |
A PSQL variable to receive the return value |
Executes an executable stored procedure, taking a list of one or more input parameters, if they are defined for the procedure, and returning a one-row set of output values, if they are defined for the procedure.