PL/pgSQL OUT Mode
A friend asked me a question about using the OUT mode parameter versus INOUT mode parameters in functions. He formed an opinion that they didn’t work in PostgreSQL PL/pgSQL.
Unfortunately, there’s not a lot of material written about how to use the OUT mode parameter in functions. I thought an article showing the standard example with a call to the function might help. The standard example function from the PostgreSQL documentation is:
1 2 3 4 5 6 7 8 9 | CREATE OR REPLACE FUNCTION sales_tax( IN amount real , OUT tax real ) AS $$ BEGIN /* Calculate the tax at 6%. */ tax := amount * 0.06; END; $$ LANGUAGE plpgsql; |
The RETURNS clause is optional but here’s how you can include it. The following example works exactly like the former.
1 2 3 4 5 6 7 8 9 10 | CREATE OR REPLACE FUNCTION sales_tax( IN amount real , OUT tax real ) RETURNS real AS $$ BEGIN /* Calculate the tax at 6%. */ tax := amount * 0.06; END; $$ LANGUAGE plpgsql; |
You call the PostgreSQL sales_tax() function like this:
SELECT 'Sales Tax ['|| sales_tax(200) ||']' AS "Return Value"; |
It should return the following:
Return Value ---------------- Sales Tax [12] (1 row) |
You can also call it in an inline code block (e.g., what Oracle documentation calls an anonymous block), like:
1 2 3 4 5 6 7 8 9 10 11 12 13 | DO $$ DECLARE /* Declare a local variable. */ tax_paid real := 0; BEGIN /* Query the results into a local variable. */ SELECT sales_tax(100) INTO tax_paid; /* Print the taxes paid. */ RAISE NOTICE 'Tax Paid [%]',tax_paid; END; $$; |
You can replace the sales_tax function with its OUT mode tax parameter with the following classic sales_tax function, which adds a tax_rate parameter.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | CREATE OR REPLACE FUNCTION sales_tax( IN amount real , IN tax_rate real ) RETURNS real AS $$ DECLARE /* Declare a local variable. */ tax real; BEGIN /* Calculate the tax at 6%. */ tax := amount * (tax_rate / 100); /* Return the tax. */ RETURN tax; END; $$ LANGUAGE plpgsql; |
Let’s return the original approach with the OUT parameter. Then, let’s expand the list of parameters to include an INOUT mode state variable, like:
1 2 3 4 5 6 7 8 9 10 11 12 | CREATE OR REPLACE FUNCTION sales_tax( IN amount real , IN tax_rate real , INOUT state VARCHAR(14) , OUT tax real ) RETURNS RECORD AS $$ BEGIN /* Calculate the tax at 6%. */ tax := amount * (tax_rate / 100); END; $$ LANGUAGE plpgsql; |
You don’t need to include the RETURNS RECORD phrase because PL/pgSQL implements a very mature adapter pattern and it adjusts the return type automatically to the parameter list. On the other hand, many beginning programmers and support staff won’t know that. That’s why I recommend you include it for clarity.
You can call this in a query with a column alias, like:
SELECT 'Sales Tax ['|| sales_tax(100,8.25,'California') ||']' AS "Return Value"; |
It will return a tuple:
Return Value ------------------------------- Sales Tax [(California,8.25)] (1 row) |
You can implement it inside an inline block by adding a local variable of the RECORD data type, like:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | DO $$ DECLARE /* Declare a local variable. */ state varchar(14) := 'California'; tax_paid real := 0; result RECORD; BEGIN /* Query the results into a local variable. */ SELECT sales_tax(100,8.25,state) INTO result; /* Print the taxes paid. */ RAISE NOTICE 'Tax Paid [%]', result; END; $$; |
It prints the following:
NOTICE: Tax Paid [("(California,8.25)")] |
You can actually return the individual members of the tuple by putting the function call inside the FROM clause, like:
SELECT * FROM sales_tax(100,8.25,'California'); |
It now returns the members of the tuple in separate columns:
state | tax ------------+------ California | 8.25 (1 row) |
Alternatively, you can call it from inside an inline block, like:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | DO $$ DECLARE /* Declare a local variable. */ state varchar(14) := 'California'; tax_paid real := 0; BEGIN /* Query the results into a local variable. */ SELECT * INTO state, tax_paid FROM sales_tax(100,8.25,state); /* Print the taxes paid. */ RAISE NOTICE 'Tax Paid [%] [%]', state, tax_paid; END; $$; |
It prints:
NOTICE: Tax Paid [California] [8.25] |
Now, let’s rewrite the function into a traditional function with all IN mode variables that returns a RECORD structure with additional values. Just one quick caveat (the big but), you can only assign values to dynamically constructed RECORD structures by using the SELECT-INTO or FOR statements. Below is the refactored sales_tax() function:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | CREATE OR REPLACE FUNCTION sales_tax( IN subtotal REAL , IN tax_rate REAL , IN state VARCHAR(14)) RETURNS RECORD AS $$ DECLARE /* Declare a local variable. */ tax REAL; tax_record RECORD; BEGIN /* Calculate the tax at 6%. */ tax := subtotal * (tax_rate / 100); /* Assign state to record. */ SELECT state, tax INTO tax_record; /* Return the tax. */ RETURN tax_record; END; $$ LANGUAGE plpgsql; |
It returns the same set of values as the early version with the four parameter example above but you only need three IN-only mode variables to get the result. Other than the parameter lists, the biggest change appears to be the assignment line, which is required in the explicit and traditional function that has only IN mode parameters:
16 | SELECT state, tax INTO tax_record; |
Given you can return any RECORD structure you want, why use INOUT and OUT mode parameters? Don’t you loose clarity about what your stored function does? Or, at least, don’t you make understanding the program logic more difficult when you use INOUT and OUT mode variables? The only benefit appears to be when you shift your input variables from the SELECT clause to the INTO clause.
Hopefully, this shows folks how to use the OUT mode parameter; and how closely related it is to a classic function.
Subscribe via RSS