Postgres return table

Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

With some exceptions, any collection of SQL commands can be joined together and defined as a “function.” Postgresql functions adds features that can make a developer’s job much simpler. Creating specific functions using the SELECT statement can reduce the number of separate calls used to retrieve specific information from a database. This tutorial will provide explanations and examples on how to use the Postgres return table function with the SELECT statement.

Prerequisites

  • Postgres must be properly install and configure on the local OS to execute the Postgres return table function. The latest version of PostgreSQL can be obtained here.

Create Sample Data Set

This section will demonstrate how to create a sample data set used for the examples in this tutorial.

First, execute the following command to create a database:

1
CREATE DATABASE product

Next, connect to the database using the psql \c product command. Now create a table as follows:

1
2
3
4
5
6
7
CREATE TABLE IF NOT EXISTS stock (
    id SERIAL PRIMARY KEY,
    product_name CHARACTER VARYING(100),
    sku CHARACTER VARYING(100),
    description CHARACTER VARYING(100),
    quantity INT
);

Now insert the following records into the stock table:

1
2
3
4
5
6
7
INSERT INTO stock (id, product_name, sku, description, quantity)
VALUES
   (1,'Face Mask','TP031', 'Face mask protection', 250),
   (2,'Foot Cover','TP036', 'Cover for the foot', 230),
   (3,'Head Gear','TP039', 'Protective head gear', 240),
   (4,'Rubber Boots','PR0321', 'Electricity protective gear', 100),
   (5,'Back Pack','PR0321', 'Ordinary Back pack', 100);

The resulting table should now contain the following data:

1
2
3
4
5
6
7
8
9
product=# SELECT * FROM stock;
 id | product_name |  sku   |         description         | quantity
----+--------------+--------+-----------------------------+----------
  1 | Face Mask    | TP031  | Face mask protection        |      250
  2 | Foot Cover   | TP036  | Cover FOR the foot          |      230
  3 | Head Gear    | TP039  | Protective head gear        |      240
  4 | Rubber Boots | PR0321 | Electricity protective gear |      100
  5 | Back Pack    | PR0321 | Ordinary Back pack          |      100
(5 ROWS)

Postgres PLpgsql Return Table

This section will examine the Postgres PLpgsql functions that is used to return the table. The following function will return all of the items in the Stock table that match the product name as a defined pattern via the ILIKE operator:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
CREATE OR REPLACE FUNCTION get_stock (prod_pattern VARCHAR)
   RETURNS TABLE (
      prod_name VARCHAR,
      pro_quantity INT
)
AS $$
BEGIN
   RETURN QUERY SELECT
      product_name,
      quantity
   FROM
      stock
   WHERE
      product_name ILIKE prod_pattern ;
END; $$
 
LANGUAGE 'plpgsql';

Following is a breakdown of the above Postgres PLpgsql function:

  • A function named get_stock is created, This requires a single-parameter ‘prod_pattern’ that defines the pattern designed to match the product’s name.

  • The function returns a table with the two columns ‘prod_name’ and ‘prod_quantity’ via the RETURN TABLE phrase.

  • A result set is then returned from the SELECT statement. Here it is important to confirm that the same data type was returned from the result set as in the tables declared in the RETURN TABLE phrase.

The Postgres PLpgsql function can be tested with the following statement:

1
2
3
4
SELECT
   *
FROM
   get_stock ('Ba%');

Here the get_stock(varchar) function retrieves all of the stock containing product_namehave ‘Ba’.

The final results should look like the following:

1
2
3
4
 prod_name | pro_quantity
-----------+--------------
 Back Pack |          100
(1 row)

Conclusion

This tutorial provided explanations and examples on how to execute the Postgres return table function using the SELECT statement. The tutorial explained how to create a sample data set and provided the command used to create a database. The article provided explanations on how to connect to the database using the psql \c product command, how to create a table and insert records into the table. The tutorial then examined the Postgres PLpgsql functions used to return the table and provided a detailed breakdown of that function. The tutorial then demonstrated how to determine if the function was executed properly. Remember that it is important to verify that the same data type was returned from the result set as is in the tables declared in the RETURN TABLE phrase when defining a pattern via the ILIKE operator.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.