Basic Postgres Procedure

To develop a basic procedure, there is a table with basic scalar types Postgres and corresponding internal platform types:

Basic Postgres scalar type	Pseudonym for Postgres type	Internal platform type
smallint	int2	bigint
integer	int int4	bigint
bigint	int8	bigint
numeric[(p, s)]	decimal[(p, s)]	text
real	float4	double
double precision	float8	double
boolean	bool	bigint
bytea	-	bytea
character[(n)]	char[(n)]	text
character varying [(n)]	varchar[(n)]	text
text	-	text
date	-	text
timestamp [(p)] without time zone	timestamptz	text

Detailed description of types is available in Postgres documentation.

Basic Composite and Table Type

Types, tables and procedures are created in the standard database language.

Basic composite type is a type that consists of basic scalar types. A basic composite type must not contain other composite type. It is important not to confuse composite type with the ROWTYPE or RECORD type. A basic composite type must be explicitly declared by means of the CREATE TYPE structure:

CREATE TYPE <type name> AS (
<column name> <column type>
);

A basic table type for input and output parameters of basic stored procedure uses the basic composite type that is set using the syntax:

CREATE FUNCTION <function name>(<input arguments>) RETURNS SETOF <name of created type> <procedure code>;

IMPORTANT. Other syntax structures for declaring input and output table parameters are not allowed.

Example

Output table structure after procedure execution:

id (int). Identifier.
name (varchar). Fruit name.

Create a table:

CREATE TABLE fruit (
id SERIAL PRIMARY KEY,
name VARCHAR(200) NOT Null
);

The procedure without input parameters that returns all rows of the fruits table:

CREATE FUNCTION fruits_take_all()
  RETURNS SETOF fruit
LANGUAGE plpgsql
AS $$
DECLARE
    ups_row fruit;
    BEGIN
        RETURN QUERY SELECT * FROM fruit ORDER BY id;
    END
$$;
SELECT * FROM fruits_take_all();

The procedure that receives the "id" input scalar parameter of the integer type extracts a row from the fruits table:

Create a parameter:

CREATE TYPE fruit_id AS (id INTEGER);

Procedure:

CREATE FUNCTION fruit_get(fruit_id INTEGER)
RETURNS SETOF fruit
LANGUAGE plpgsql
AS $$
    BEGIN
        RETURN QUERY SELECT * FROM fruit WHERE id = fruit_id;
    END
$$;
SELECT * FROM fruit_get(fruit_id := 13);