Shulou(Shulou.com)06/01 Report--

PgSQL has supported SRF (Set Returning Func. Collection return function) with some new function permission options

Make schema settings more flexible. SRF in addition to the built-in function generate_series generate_subscript mentioned in the manual, custom functions can also return collections. The following example is extracted from how PgSQL wiki: PL/PgSQL returns multiple rows of results

Let's start with dealing with simple form functions.

Create table department (id int primary key, name text); create table employee (id int primary key, name text, salary int, departmentid int references department); insert into department values (1, 'Management'); insert into department values (2,' IT'); insert into employee values (1, 'John Smith', 30000, 1); insert into employee values (2,' Jane Doe', 50000, 1); insert into employee values (3, 'Jack Jackson', 60000, 2)

The data type that SRF can return can be a rowtype defined in an existing table or a generic record type.

First, let's look at a simple SQL function that returns rowtype from an existing table.

Create function GetEmployees () returns setof employee as' select * from employee;' language 'sql'

This very simple function directly returns all lines in employee:

Its return type is setof employee, that is, it returns a set of rows composed of employee rows.

The body uses a simple SQL statement to generate the output line.

SRF can replace tables or subqueries in FROM in a query.

For example, use the function to return all employees with id > 2 as long as:

Select * from GetEmployees () where id > 2

Fine, but what about returning more complex data?

For example: the list of departments and the salaries of all the employees in it

To return to an existing record type, you need to create a fictional type to hold the output data

For example:

Create type holder as (departmentid int, totalsalary int8)

Here a new compound type holder is created by an int named departmentid

And a bigint named totalsalary, we can have the function return this type of collection:

This time we use SQL and PL/pgSQL to implement this function respectively:

Create function SqlDepartmentSalaries () returns setof holder as'select departmentid, sum (salary) as totalsalary from GetEmployees () group by departmentid'language 'sql';create or replace function PLpgSQLDepartmentSalaries () returns setof holder as'declare r holder%rowtype;begin for r in select departmentid, sum (salary) as totalsalary from GetEmployees () group by departmentid loop return next r; end loop; return;end'language' plpgsql'

The version of SQL is very similar to the previous version, returning the rowtype defined by the holder (int, int8) type

The rows returned are determined by the group by query in the function body.

The PL/pgSQL version is slightly more complex. First, the variable r is declared as rowtype holder. Use this variable to save the query results in the row function body, the function body executes the result of the group by query in a loop, r is assigned to each row in the result in turn, and the new return form 'return next' is used in the loop body to append the result to the returned set, but does not cause the function to return. Currently, the SRF function of PL/pgSQL will not return until all the results have been generated. If the collection is very large, write to the hard disk. This limitation may change in future versions.

The new function is used the same as before

Select * from PLpgSQLDepartmentSalaries ()

The PL/pgSQL function can also operate on the results, returning only some results.

For example, to calculate the department's operating costs: 75% of the department's total salary is over 70000, and the rest is 50%.

Returns the department id for departments with salary + expenses > 100000.

Create or replace function ExpensiveDepartments () returns setof int as'declare r holder%rowtype;begin for r in select departmentid, sum (salary) as totalsalary from GetEmployees () group by departmentid loop if (r.totalsalary > 70000) then r.totalsalary: = CAST (r.totalsalary * 1.75 as int8); else r.totalsalary: = CAST (r.totalsalary * 1.5 as int8); end if If (r.totalsalary > 100000) then return next r. Departmentid; end if; end loop;return;end'language 'plpgsql'

Compare the difference between this time and previous PLpgSQLDepartmentSales ().

Because this time you only need to return the department id of the high-cost department.

Function returns a collection of integers (department id) instead of the previous composite type.

If (r.totalsalary > 70000) then r.totalsalary: = CAST (r.totalsalary * 1.75as int8); else r.totalsalary: = CAST (r.totalsalary * 1.5as int8); end if

Then determine whether the totalsalary is greater than 100000. If true, return the identifier.

If (r.totalsalary > 100000) thenreturn next r. Departmentativeend if

Note that this time return next does not return record r, but only departmentid

If you need to return the sum of total payroll and expenses at the same time

It can be defined as return setof holder in the previous declaration, where return next r is used

The compound type returned by the above function is used provided that the returned type is the same as in the function's return declaration.

If it is different, the SQL version will report an error when it is created, and the PL/pgSQL version will have an error while running.

But what if the type in the result can only be determined at run time?

At this point you can declare return setof record to return a collection of composite types

The returned type can be set when called. For example, we want to create a function that returns all the rows in the specified table:

Create or replace function GetRows (text) returns setof record as'declare r record;begin for r in EXECUTE''select * from''| | $1 loop return next r; end loop;return;end'language 'plpgsql'

Calling this function is slightly more complex than before, and you need to specify the data returned by the function in the query.

PostgreSQL treats the SRF function like a subquery and syntactically similar to the alias setting in a subquery.

Select * from GetRows ('Department') as dept (deptid int, deptname text)

We pass in Department as an argument, and the result should be the same as the general record of the Department table

It consists of an INT and a TEXT. So we told PgSQL that dept was an alias.

Contains an integer named deptid and text for deptname.

Finally, let's try to generate data entirely using the PL/pgSQL function. Let's start with the simplest:

Write a function that receives all the numbers that return between 1 and any number, and this twice as much as them.

Let's first write a version with predefined types as internal and return types.

Create type numtype as (num int, doublenum int); create or replace function GetNum (int) returns setof numtype as'declare r numtype%rowtype; I int;begin for i in 1. $1 loop r.num: = I; r.doublenum: = iTunes 2; return next r; end loop; return;end'language 'plpgsql'

The function is very simple, and r in the declaration is a custom rowtype named numtype.

Assign each value between 1 and parameters to num and doublenum

Then return next r adds the result to the queue of the output set

Using record types can achieve a general effect, eliminating the type declaration outside the function.

But it's more complicated to do and requires one more select call.

Similarly, there are dynamic SQL query statements that return multiple results.

(PREPARE STATEMENT... + EXECUTE...INTO...USING + DEALLOCATE PREPARE)

You can also return multiple rows of results by returning a pointer.

Https://www.postgresql.org/docs/current/static/ecpg-dynamic.html

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.