In this Postgresql tutorial, we will learn about the “Postgresql function return table“, the function will return columns, tables, records, etc. Also, we are going to cover the following topics.
- Postgresql function return table
- Postgresql function return table with dynamic columns
- Postgresql function return table all columns
- Postgresql function return table with columns
- Postgresql function return table record
- Postgresql function return table and out parameter
- Postgresql function return table variable
- Postgresql function return table execute
- Postgresql function return table insert
Postgresql function return table
To create a function in Postgresql that can return table, record, column, etc, we need to know about a command CREATE OR REPLACE FUNCTION that defines a new function or, replace the existing one in the Postgresql database.
syntax:
CREATE [OR REPLACE] FUNCTION function_name (arguments)
RETURNS name_of_return_datatype AS $variable_name$
DECLARE
declaration;
BEGIN
[ function_body ]
RETURN { variable_name | value }
END; LANGUAGE plpgsql;
Where,
function-name: It is used to specify the name of the function.
[OR REPLACE]: It is an option that allows us to modify an existing function.
The function should contain a return statement.
RETURN: It is a clause that specifies the data type we are going to return from the function. The name_of_return_datatype can correspond to the type of a table column or be a base, composite, or domain type.
function-body: It contains the executable part.
AS: It is a keyword that is used for creating a standalone function.
plpgsql: It is the name of the language that the function is implemented in.
Let’s understand through an example by creating a new table.
Create a table named emp_info that will contain information about employees.
CREATE TABLE emp_info(id SERIAL,name VARCHAR,age INT,address VARCHAR,salary_$ INT);
Insert the following records.
INSERT INTO emp_info(id,name,age,address,salary_$)VALUES(1,'Iris',32,'California',25000),
(2,'Dan',45,'Boston',45000),(3,'Gregory',23,'LA',24000),(4,'Lillian',50,'New York',50000),
(5,'Jill F',34,'Canada',28000),(6,'Robert',29,'Brazil',20000);
SELECT * FORM emp_info;

Now create the function named emp_name to return the name of the employee by providing id.
CREATE OR REPLACE FUNCTION emp_name (emp_id int)
RETURNS varchar AS $e_name$
DECLARE
ep_name varchar;
BEGIN
SELECT name into ep_name FROM emp_info WHERE id = emp_id;
RETURN ep_name;
END;
$e_name$ LANGUAGE plpgsql;
Let’s execute the emp_name function to the name of the employee whose id is 2.
SELECT * FROM emp_name(2);

In the above output, we have found the name of the employee and that is Dan.
Read: Postgresql ilike case insensitive
Postgresql function return table with dynamic columns
In Postgresql, the function can return a table with columns by supplying column names to function dynamically using Execute command. Execute command help in running dynamic Postgresql queries within the function.
Let’s understand with an example.
CREATE OR REPLACE FUNCTION get_col (_col_name VARCHAR)
RETURNS TABLE (
col_name VARCHAR)
AS $$
BEGIN
RETURN QUERY EXECUTE format('SELECT
%I
FROM
emp_info',_col_name );
END; $$
LANGUAGE 'plpgsql';
Run the above function by providing a column name.
SELECT get_col('name');

Read: Postgresql group_concat
Postgresql function return table all columns
In Postgresql, the function can also return a table with all columns, so we will modify the above function to return a table with all columns.
Use the below code.
CREATE OR REPLACE FUNCTION emp_name ()
RETURNS TABLE (emp_id int,emp_name varchar,
emp_age int,emp_address varchar,
emp_salary int)
AS $e_name$
BEGIN
RETURN QUERY SELECT * FROM emp_info;
END;
$e_name$ LANGUAGE plpgsql;
In the above code, we are changing the function return type as a TABLE with the columns data type, because we want the function to return the table with all columns, so we have specified the data type of each column in the table function.
In the BEGIN body, we using RETURN QUERY that appends the results of the SELECT statement to the function result set. Using the SELECT statement, we are fetching all the columns from a table named emp_info.
Let’s call the function.
SELECT * FROM emp_name();

In the above output, we have successfully returned the table with all columns.
Read: How to migrate from MySQL to Postgres
Postgresql function return table with columns
In Postgresql, the function can also return a table with specific columns or columns that we want from a table.
Now we will again modify the above function to return a table with columns.
CREATE OR REPLACE FUNCTION emp_name ()
RETURNS TABLE (emp_name varchar,
emp_address varchar)
AS $e_name$
BEGIN
RETURN QUERY SELECT name,address FROM emp_info;
END;
$e_name$ LANGUAGE plpgsql;
In the above code, we are changing the function return type as a TABLE with the columns data type, because we want the function to return the table with specific columns, so we have specified the data type of two columns in the table function.
Between the BEGIN and END bodies, we using RETURN QUERY that appends the results of the SELECT statement to the function result set. Using the SELECT statement, we are fetching two columns named name, address from a table named emp_info.
Let’s execute the above function.
SELECT * FROM emp_name();

Read: Postgresql listen_addresses
Postgresql function return table record
In Postgresql, the function can return table records using the RECORD variable of the Postgresql database. RECORD is a variable that acts as a placeholder for a row of results set from the query.
Use the below code to return table records from a function named emp_function.
CREATE OR REPLACE
FUNCTION emp_function(_id int)
RETURNS record
AS $$
DECLARE
data_record record;
BEGIN
SELECT * INTO data_record FROM emp_info WHERE id =_id;
RETURN data_record;
END;
$$ LANGUAGE plpgsql;
In the above code, the RETURNS type of emp_function is a record, in DECLARE section data_record is a variable of record type that stores the result of the SELECT statement, which is in BEGIN section of the function named emp_function.
Now call the emp_function to return the table record.
SELECT emp_function(1); -- To get record of the user whose id is 1

In the above output, we can see the record of the user whose id is 1.
Read: Postgresql current_timestamp
Postgresql function return table and out parameter
In Postgresql, we can also provide parameters IN and OUT. where IN is input and OUT is output.
With help of IN, we pass the input parameter to function. OUT can be used when we want that function will return something, in the case of OUT, we don’t specify the RETURN data type.
Let’s create a function that will calculate the 6% tax of any amount.
CREATE OR REPLACE
FUNCTION percent_sales_tax( IN amount REAL
, OUT tax_amount REAL )
AS $$
BEGIN
tax_amount := amount * 0.06;
END;
$$ LANGUAGE plpgsql;
In the above code, we have created a function named percent_sales_tax that can calculate the tax amount of any amount, and it accepts two parameter amount and tax_amount with the data type of REAL respectively. we have used the IN and OUT for getting input into and out from the function respectively.
Execute the above function.
SELECT * FROM percent_sales_tax(25000);

In the above output, we have calculated the 6% tax amount of 25000 which is 1500 using the percent_sales_tax function.
Read: Postgresql row_number
Postgresql function return table variable
In Postgresql, the variable is a value that can change or can accept any value depending upon the condition. So we can also use the variable in a function to store the value and return the variable.
Let’s modify the function percent_sales_tax for a variable that we created in the previous sub-section.
CREATE OR REPLACE
FUNCTION percent_sales_tax( IN amount REAL)
RETURNS REAL
AS $$
DECLARE
tax_amount REAL;
BEGIN
SELECT amount * 0.06 INTO tax_amount;
RETURN tax_amount;
END;
$$ LANGUAGE plpgsql;
In the above code DECLARE section, we have created the new variable named tax_amount with the REAL data type, and in BEGIN body, calculating the 6% tax amount and storing the result in a variable named tax_amount using SELECT statement, then returning the variable using RETURN statement in BEGIN.
Let’s run the above function.
SELECT * FROM percent_sales_tax(25000);

Read: Postgresql auto increment
Postgresql function return table execute
In Postgresql, the function can return a table using the execute the command within a function, with help of executing the command, we can generate dynamic commands that can inset or fetch different kinds of information from the table, with different data types each time they are executed.
Let’s get the age of the employee by providing a name to function.
CREATE OR REPLACE FUNCTION get_name_age (_name VARCHAR)
RETURNS TABLE (
emp_age INT)
AS $$
BEGIN
RETURN QUERY EXECUTE 'SELECT
age
FROM
emp_info
WHERE
name = $1 ;' USING _name;
END; $$
LANGUAGE 'plpgsql';
In the above code within BEGIN and END, we are using EXECUTE command to execute the dynamic Postgresql queries. The command string can use parameter values, which are denoted in the command as $1, etc. These symbols refer to values that we supplied in the USING clause
Syntax:
EXECUTE command-string USING expression ;
Where,
command-string: It is a string containing the command to be executed.
Using: It is an expression, values are supplied using this expression, that is inserted into the commands.
Call the above function to get the age of the employee named “Dan”.
SELECT get_name_age('Dan');

In the above code, we have found the age of Dan which is 45 years.
Read: PostgreSQL CREATE INDEX
Postgresql function return table insert
In Postgresql, the function can return a table after inserting some values in the table.
Run the below code.
CREATE OR REPLACE FUNCTION insert_data(_emp_name VARCHAR,_emp_age INT,_emp_address VARCHAR,_emp_salary INT)
RETURNS TABLE (
emp_id INT,
emp_name VARCHAR,
emp_age INT,
emp_address VARCHAR,
emp_salary INT)
AS $$
BEGIN
INSERT INTO emp_info(name,age,address,salary_$) VALUES(_emp_name,_emp_age,_emp_address,_emp_salary);
RETURN QUERY SELECT * FROM emp_info;
END; $$
LANGUAGE 'plpgsql';
Run the above function.
SELECT insert_data('Rony',20,'California',50000);

You may also like to read the following PostgreSQL tutorials.
- Postgresql import SQL file
- PostgreSQL list users
- Postgresql generate_series
- Postgresql cast int
- Update query in PostgreSQL
- Postgresql date_trunc function
So in this tutorial, we have learned about the “Postgresql function return table” and covered the following topics.
- Postgresql function return table with dynamic columns
- Postgresql function return table all columns
- Postgresql function return table with columns
- Postgresql function return table record
- Postgresql function return table and out parameter
- Postgresql function return table variable
- Postgresql function return table execute
- Postgresql function return table insert
I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.
Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.