About
This howto shows you how to return a result set from a SQL function in Postgres.
You can therefore create dynamically a SQL Query statement
This example has been adapted from the official documentation - Obtaining a ResultSet from a stored function to add a argument.
Steps
The SQL Function Statement
The SQL statement that create the sql function is:
tabul data print --type text --pipe-mode postgres/setof-create-func.sql@howto
CREATE OR REPLACE FUNCTION setoffunc(int) RETURNS SETOF int AS
$$
SELECT 1 + $1
UNION
SELECT 2 + $1;
$$ LANGUAGE sql
The SETOF modifier indicates that the function will return a set of items, rather than a single item. More information at: Function as Returning Sets
The SQL Function Creation
tabul data print '(postgres/setof-create-func.sql@howto)@postgres'
# The quotes are only mandatory in bash because parenthesis are a bash token (ie subshell)
(postgres/setof-create-func.sql@howto)@postgres
id count statement line error_code error_message
-- ----- -------------------------------------------------- ---- ---------- -------------
1 0 CREATE OR REPLACE FUNCTION setoffunc(int) RETURNS 1 0
Execution
With a literal SQL Query statement
The SQL Query to execute the SETOF sql function is:
tabul data print --type text --pipe-mode postgres/setof-select.sql@howto
SELECT *
FROM setoffunc(1)
We will execute this statement by printing it
tabul data print '(postgres/setof-select.sql@howto)@postgres'
# The quotes are only mandatory in bash because parenthesis are a bash token (ie subshell)
Output:
(postgres/setof-select.sql@howto)@postgres
setoffunc
---------
2
3
With a parametrized SQL Query statement
The SQL query parametrized to execute the SETOF sql function is:
tabul data print --type text --pipe-mode postgres/setof-select-parametrized.sql@howto
SELECT *
FROM setoffunc(?)
To make this statement executable, we need to create a sql request manifest so that we can define the parameters
kind: sql-request
spec:
# the executable data uri
# * takes the file (setof-select-parametrized)
# * located in the same directory as this manifest (md)
# * and execute it against (postgres)
data-uri: (setof-select-parametrized.sql@md)@postgres
# the data definition
data-def:
parameters:
# the parameter for the placeholder
- value: 2 # the value passed to the upper function
direction: in # set the value
type: integer # the type of the value
We will execute this manifest by printing it
tabul data print postgres/setof-select-parametrized--sql-request.yml@howto
Output:
(setof-select-parametrized.sql@md)@postgres
setoffunc
---------
3
4
Next
More information on setof functions can be found in the Postgres documentation at the section SQL Functions Returning Sets.