This is part two of the little series about server side programming in PostgreSQL. In the last post we had a look at the basics when it comes to SQL functions. We learned how to write those functions, how we can pass parameters to functions, how to name parameters for better readability, how to return a result, and how we can use functions in plain SQL statements. In this post we’ll go a step further and will have a look at how you can use sets, user defined defined types, and polymorphic types. What we will see here is not only true for SQL functions, but also for PL/pgSQL procedures and functions as we will see in another post.
If we go back to one of the function we’ve defined in the last post:
postgres=# create or replace function get_bid_for_aid (pn_aid int) returns int postgres-# as $my_tag$ postgres$# select bid from pgbench_accounts where aid = pn_aid; postgres$# $my_tag$ language SQL; CREATE FUNCTION
This function is returning exactly one result (or nothing, when there is nothing found for a given aid). This might be what you want, but sometimes it is more convenient to return a whole result set (and that is what SQL is made for, it is a set based language). What do we need to change, to make this function returning a result set instead of a single row? Here is one way of doing it:
postgres=# create or replace function get_bid_for_aid_set (pn_aid_from pgbench_accounts.aid%type, pn_aid_to pgbench_accounts.aid%type) returns setof pgbench_accounts.bid%type postgres-# as $my_tag$ postgres$# select bid from pgbench_accounts postgres$# where aid >= pn_aid_from postgres$# and aid <= pn_aid_to; postgres$# $my_tag$ language SQL; NOTICE: type reference pgbench_accounts.aid%TYPE converted to integer NOTICE: type reference pgbench_accounts.aid%TYPE converted to integer NOTICE: type reference pgbench_accounts.bid%TYPE converted to integer CREATE FUNCTION
Here is what changed:
- We added one additional parameter and changed the parameters from a static data type to dynamically use the data type of the underlying column
- What the function will be returning is a set of bids, which means potentially more than one row
- Of course the statement needs to fetch more than one row, so this has changed as well
You may have noticed that PostgreSQL automatically converted the data types of the parameters and the return data type to integer. You’ll also see that when you describe the function:
postgres=# df get_bid_for_aid_set
List of functions
Schema | Name | Result data type | Argument data types | Type
--------+---------------------+------------------+----------------------------------------+------
public | get_bid_for_aid_set | SETOF integer | pn_aid_from integer, pn_aid_to integer | func
(1 row)
Once we call the new function we’ll see that the return is a set:
postgres=# select * from get_bid_for_aid_set(pn_aid_from=>1,pn_aid_to=>10);
get_bid_for_aid_set
---------------------
1
1
1
1
1
1
1
1
1
(9 rows)
This is, of course, much better then using the previous function that only return one row for each input as it reduces the amount of calls from ten to one. We still can ask for one row but now we flexible enough to return as much as we want. You can also return a subset of the whole table when using setof;
postgres=# create or replace function get_all_for_aid_set (pn_aid_from pgbench_accounts.aid%type, pn_aid_to pgbench_accounts.aid%type) returns setof pgbench_accounts
as $my_tag$
select * from pgbench_accounts
where aid >= pn_aid_from
and aid <= pn_aid_to;
$my_tag$ language SQL;
NOTICE: type reference pgbench_accounts.aid%TYPE converted to integer
NOTICE: type reference pgbench_accounts.aid%TYPE converted to integer
CREATE FUNCTION
This will return all columns from the table as a set:
postgres=# select * from get_all_for_aid_set(pn_aid_from=>1,pn_aid_to=>10); aid | bid | abalance | filler -----+-----+----------+-------------------------------------------------------------------------------------- 2 | 1 | 0 | 3 | 1 | 0 | 4 | 1 | 0 | 5 | 1 | 0 | 6 | 1 | 0 | 7 | 1 | 0 | 8 | 1 | 0 | 9 | 1 | 0 | 10 | 1 | 0 | (9 rows)
If you have the requirement to work with user defined data types, you can use those in functions as well. Lets consider this simple custom data type:
postgres=# create type my_type as ( a int, b int );
CREATE TYPE
postgres=# d my_type
Composite type "public.my_type"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
a | integer | | |
b | integer | | |
Using that type in a function follows exactly them same rules as if you work with the base types:
postgres=# create or replace function get_all_for_custom_type (p_my_type in my_type) returns setof pgbench_accounts
as $my_tag$
select * from pgbench_accounts
where aid >= p_my_type.a
and aid row(1,5) );
postgres=# select * from get_all_for_custom_type ( p_my_type => row(1,5) );
aid | bid | abalance | filler
-----+-----+----------+--------------------------------------------------------------------------------------
2 | 1 | 0 |
3 | 1 | 0 |
4 | 1 | 0 |
5 | 1 | 0 |
(4 rows)
Using a custom type as a return data type works, not surprises here, as well:
postgres=# create or replace function get_custom_type (pn_aid in int) returns my_type
as $my_tag$
select row(aid,bid) from pgbench_accounts
where aid = pn_aid;
$my_tag$ language SQL;
CREATE FUNCTION
postgres=# select * from get_custom_type(2);
a | b
---+---
2 | 1
(1 row)
Another way of returning a set is to the use “TABLE” syntax:
postgres=# create or replace function get_all_table (pn_aid_from pgbench_accounts.aid%type, pn_aid_to pgbench_accounts.aid%type) returns table(aid int,bid int)
as $my_tag$
select aid,bid from pgbench_accounts
where aid >= pn_aid_from
and aid <= pn_aid_to;
$my_tag$ language SQL;
NOTICE: type reference pgbench_accounts.aid%TYPE converted to integer
NOTICE: type reference pgbench_accounts.aid%TYPE converted to integer
CREATE FUNCTION
postgres=# select * from get_all_table(2,5);
aid | bid
-----+-----
2 | 1
3 | 1
4 | 1
5 | 1
(4 rows)
In addition to what we’ve already seen, PostgreSQL comes with Polymorphic Types. Using these types you can make a function accepting almost any data type as it’s input and return:
postgres=# create or replace function get_polymorphic (anyelement, anyelement) returns table(aid int,bid int)
as $my_tag$
select aid,bid from pgbench_accounts
where aid >= $1
and aid <= $2;
$my_tag$ language SQL;
CREATE FUNCTION
postgres=# select * from get_polymorphic(2,6);
aid | bid
-----+-----
2 | 1
3 | 1
4 | 1
5 | 1
6 | 1
(5 rows)
That’s it for now. In the next post we’ll start taking a look at PL/pgSQL.
![Thumbnail [60x60]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/DWE_web-min-scaled.jpg)