Activate Your Return Types Meaning

0 views

Skip to first unread message

Janne Desir

unread,

Aug 3, 2024, 4:19:40 PM8/3/24

to luotufasing

PL/pgSQL can be used to define trigger functions on data changes or database events. A trigger function is created with the CREATE FUNCTION command, declaring it as a function with no arguments and a return type of trigger (for data change triggers) or event_trigger (for database event triggers). Special local variables named TG_something are automatically defined to describe the condition that triggered the call.

Row-level triggers fired BEFORE can return null to signal the trigger manager to skip the rest of the operation for this row (i.e., subsequent triggers are not fired, and the INSERT/UPDATE/DELETE does not occur for this row). If a nonnull value is returned then the operation proceeds with that row value. Returning a row value different from the original value of NEW alters the row that will be inserted or updated. Thus, if the trigger function wants the triggering action to succeed normally without altering the row value, NEW (or a value equal thereto) has to be returned. To alter the row to be stored, it is possible to replace single values directly in NEW and return the modified NEW, or to build a complete new record/row to return. In the case of a before-trigger on DELETE, the returned value has no direct effect, but it has to be nonnull to allow the trigger action to proceed. Note that NEW is null in DELETE triggers, so returning that is usually not sensible. The usual idiom in DELETE triggers is to return OLD.

INSTEAD OF triggers (which are always row-level triggers, and may only be used on views) can return null to signal that they did not perform any updates, and that the rest of the operation for this row should be skipped (i.e., subsequent triggers are not fired, and the row is not counted in the rows-affected status for the surrounding INSERT/UPDATE/DELETE). Otherwise a nonnull value should be returned, to signal that the trigger performed the requested operation. For INSERT and UPDATE operations, the return value should be NEW, which the trigger function may modify to support INSERT RETURNING and UPDATE RETURNING (this will also affect the row value passed to any subsequent triggers, or passed to a special EXCLUDED alias reference within an INSERT statement with an ON CONFLICT DO UPDATE clause). For DELETE operations, the return value should be OLD.

The return value of a row-level trigger fired AFTER or a statement-level trigger fired BEFORE or AFTER is always ignored; it might as well be null. However, any of these types of triggers might still abort the entire operation by raising an error.

This example trigger ensures that any time a row is inserted or updated in the table, the current user name and time are stamped into the row. And it checks that an employee's name is given and that the salary is a positive value.

Another way to log changes to a table involves creating a new table that holds a row for each insert, update, or delete that occurs. This approach can be thought of as auditing changes to a table. Example 43.4 shows an example of an audit trigger function in PL/pgSQL.

This example trigger ensures that any insert, update or delete of a row in the emp table is recorded (i.e., audited) in the emp_audit table. The current time and user name are stamped into the row, together with the type of operation performed on it.

A variation of the previous example uses a view joining the main table to the audit table, to show when each entry was last modified. This approach still records the full audit trail of changes to the table, but also presents a simplified view of the audit trail, showing just the last modified timestamp derived from the audit trail for each entry. Example 43.5 shows an example of an audit trigger on a view in PL/pgSQL.

This example uses a trigger on the view to make it updatable, and ensure that any insert, update or delete of a row in the view is recorded (i.e., audited) in the emp_audit table. The current time and user name are recorded, together with the type of operation performed, and the view displays the last modified time of each row.

AFTER triggers can also make use of transition tables to inspect the entire set of rows changed by the triggering statement. The CREATE TRIGGER command assigns names to one or both transition tables, and then the function can refer to those names as though they were read-only temporary tables. Example 43.7 shows an example.

This example produces the same results as Example 43.4, but instead of using a trigger that fires for every row, it uses a trigger that fires once per statement, after collecting the relevant information in a transition table. This can be significantly faster than the row-trigger approach when the invoking statement has modified many rows. Notice that we must make a separate trigger declaration for each kind of event, since the REFERENCING clauses must be different for each case. But this does not stop us from using a single trigger function if we choose. (In practice, it might be better to use three separate functions and avoid the run-time tests on TG_OP.)

PL/pgSQL can be used to define event triggers. PostgreSQL requires that a function that is to be called as an event trigger must be declared as a function with no arguments and a return type of event_trigger.

If you see anything in the documentation that is not correct, does not match your experience with the particular feature or requires further clarification, please use this form to report a documentation issue.

This page describes the client-side service available with the Maps JavaScript API. If you want to work with Google Maps web services on your server, take a look at the Node.js Client for Google Maps Services. The page at that link also introduces the Java Client, Python Client and Go Client for Google Maps Services.

The Maps JavaScript API provides a Geocoder class for geocoding and reverse geocoding dynamically from user input. If instead you wish to geocode static, known addresses, see the Geocoding web service.

Effective July 16, 2018, a new pay-as-you-go pricing plan went into effectfor Maps, Routes, and Places. To learn more about the new pricing and usagelimits for your use of the JavaScript Geocoding service, seeUsage and Billingfor the Geocoding API.

Accessing the Geocoding service is asynchronous, since the Google Maps API needs to make a call to an external server. For that reason, you need to pass a callback method to execute upon completion of the request. This callback method processes the result(s). Note that the geocoder may return more than one result.

You access the Google Maps API geocoding service within your code via the google.maps.Geocoder constructor object. The Geocoder.geocode() method initiates a request to the geocoding service, passing it a GeocoderRequest object literal containing the input terms and a callback method to execute upon receipt of the response.

The Geocoding service requires a callback method to execute upon retrieval of the geocoder's results. This callback should pass two parameters to hold the results and a status code, in that order.Note: The Geocoding response also contains plus_code and address_descriptor fields at the response level, but these are not accessible through the callback. Instead, they should be accessed through the promise. See Fulfill on Zero Results for more details.Geocoding ResultsThe GeocoderResult object represents a single geocoding result. A geocode request may return multiple result objects:

partial_match indicates that the geocoder did not returnan exact match for the original request, though it was able to match part ofthe requested address. You may wish to examine the original request for misspellings and/oran incomplete address.

Partial matches most often occur for street addresses that do not existwithin the locality you pass in the request. Partial matches may also bereturned when a request matches two or more locations in the same locality.For example, "Hillpar St, Bristol, UK" will return a partial match for bothHenry Street and Henrietta Street. Note that if a request includes amisspelled address component, the geocoding service may suggest an alternativeaddress. Suggestions triggered in this way will also be marked as a partialmatch.

The addresses will be returned by the Geocoder using the browser's preferred language setting, or the language specified when loading the API JavaScript using the language parameter. (For more information, see Localization.)

The types[] array in the GeocoderResult indicates the address type. The types[] array may also be returned within a GeocoderAddressComponent to indicate the type of the particular address component. Addresses returned by the geocoder may have multiple types; the types may be considered tags. For example, many cities are tagged with the political and locality type.

You can instruct the Geocoding Service to prefer results within a given viewport (expressed as a bounding box). You do so by setting the bounds parameter within the GeocoderRequest object literal to define the bounds of this viewport. Note that biasing only prefers results within the bounds; if more relevant results exist outside of these bounds, they may be included.

You can set the Geocoding Service to return results biased to a particular region explicitly using the region parameter. This parameter takes a region code, specified as a two-character (non-numeric) Unicode region subtag. These tags map directly to familiar ccTLD ("top-level domain") two-character values such as "uk" in "co.uk" for example. In some cases, the region tag also supports ISO-3166-1 codes, which sometimes differ from ccTLD values ("GB" for "Great Britain" for example).

Geocoding requests can be sent for every domain in which the main Google Maps application offers geocoding. Note that biasing only prefers results for a specific domain; if more relevant results exist outside of this domain, they may be included.

You can set the Geocoding Service to return address results restricted to a specific area, by using a components filter. Specify the filter in the componentRestrictions parameter. Filter values support the same methods of spelling correction and partial matching as other geocoding requests.