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:
-
:=
,=
andDEFAULT
are the same. - Trying to change the constant local variable
value4
gets error. - The uninitialized local variable
value5
isNULL
. - 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 parametersnum1 INT
andINT
(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 afterpublic.person.<column>
otherwise there is error.You can replace
RETURNS VARCHAR(20)
withRETURNS 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
withRETURNS 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)
Top comments (0)