In the previous chapter, we saw functions that return single values. These functions return either a "scalar," simple type such as an integer, text, or data; or a more complex type, similar to a row in the database table. In this chapter, we will expand these concepts and show you how to return your data to the client in more powerful ways.
We will also examine the following topics:
- Differences between SETOF scalars, rows, and arrays
- Returning CURSORs, which are kind of "lazy" tables, that is, something that can be used to get a set of rows, but which may not have actually evaluated or fetched the rows yet, as the modern world is not about rigid table-structured data
- Ways to deal with more complex data structures, both predefined and dynamically created
Let's start with a simple example and then add more features and variants as we go.
Rowsets are similar to arrays in many ways, but they mainly differ in their usage. For most data manipulations, you will want to use rowsets, as the SQL language is designed to deal with them. Arrays, however, are most useful for static storage. They are more complicated for client applications to use than rowsets, with usability features missing, such as no simple and straightforward built-in ways to iterate over them.