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)