Postgresql function return table

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