Declarations in PostgreSQL

Buy Me a Coffee☕

You can declare local variables with :=, = in DECLARE clause as shown below:

CREATE FUNCTION my_func()
RETURNS INT
AS $$
DECLARE
  value1 INT := 1; -- Here
  value2 INT = 2; -- Here
  value3 INT DEFAULT 3; -- Here
  value4 CONSTANT INT := 4; -- Here
  value5 INT; -- Here
BEGIN
  RETURN value1 + value2 + value3;
END;
$$ LANGUAGE plpgsql;

*Memos:

• :=, = and DEFAULT are the same.
• Trying to change the constant local variable value4 gets error.
• The uninitialized local variable value5 is NULL.
• You can declare local variables with DECLARE clause in a PL/pgSQL function and procedure.

*The doc explains declarations.

Then, calling my_func() returns 6 as shown below:

postgres=# SELECT my_func();
 my_func
---------
       6
(1 row)

Next, you can declare local variables with parameters in DECLARE clause as shown below:

CREATE FUNCTION my_func(num1 INT, INT, num3 INT)
RETURNS INT
AS $$
DECLARE
  value1 INT := $1; -- Here
  value2 INT := $2; -- Here
  value3 INT := num3; -- Here
BEGIN
  RETURN value1 + value2 + value3;
END;
$$ LANGUAGE plpgsql;

*Memos:

• A type-only parameter is possible like the middle parameter INT.

• Using $1 and $2 as the aliases of the parameters num1 INT and INT(The middle parameter) respectively is possible.

Then, calling my_func() returns 6 as shown below:

postgres=# SELECT my_func(1, 2, 3);
 my_func
---------
       6
(1 row)

Next, you can declare aliases with ALIAS FOR in DECLARE clause as shown below:

CREATE FUNCTION my_func(num1 INT, INT, num3 INT)
RETURNS INT
AS $$
DECLARE
  value1 ALIAS FOR $1; -- Here
  value2 ALIAS FOR $2; -- Here
  value3 ALIAS FOR num3; -- Here
BEGIN
  RETURN value1 + value2 + value3;
END;
$$ LANGUAGE plpgsql;

*Memos:

• You cannot specify type for aliases otherwise there is error.

• You can declare aliases with DECLARE clause in a PL/pgSQL function and procedure.

Then, calling my_func() returns 6 as shown below:

postgres=# SELECT my_func(1, 2, 3);
 my_func
---------
       6
(1 row)

Next for example, you create person table as shown below:

CREATE TABLE person (
  id INT,
  name VARCHAR(20)
);

Then, you insert 2 rows into person table as shown below:

INSERT INTO person (id, name) 
VALUES (1, 'John'), (2, 'David');

Then, you can declare the local variables of table columns' types in DECLARE clause as shown below:

CREATE FUNCTION my_func()
RETURNS VARCHAR(20)
AS $$
DECLARE
  person_id public.person.id%TYPE := 2; -- Here
  person_name public.person.name%TYPE; -- Here
BEGIN
  SELECT name INTO person_name FROM person WHERE id = person_id;
  RETURN person_name;
END;
$$ LANGUAGE plpgsql;

*Memos:

• You can omit the schema public..

• You must set %TYPE just after public.person.<column> otherwise there is error.

• You can replace RETURNS VARCHAR(20) with RETURNS VARCHAR.

• The doc explains the local variables of table columns' types.

Then, calling my_func() returns David as shown below:

postgres=# SELECT my_func();
 my_func
---------
 David
(1 row)

And, you can declare the local variable of a table row type in DECLARE clause as shown below:

CREATE FUNCTION my_func()
RETURNS person
AS $$
DECLARE
  person_row public.person%ROWTYPE; -- Here
  -- person_row RECORD; -- Here
BEGIN
  SELECT * INTO person_row FROM person WHERE id = 2;
  RETURN person_row;
END;
$$ LANGUAGE plpgsql;

*Memos:

• You can omit the schema public. and %ROWTYPE.

• You can also use person_row RECORD;.

• You can replace RETURNS person with RETURNS RECORD.

• The doc explains the local variable of a table row type.

Then, calling my_func() returns a row as shown below:

postgres=# SELECT my_func();
  my_func
-----------
 (2,David)
(1 row)