Declarations in PostgreSQL
Tags: postgres, declaration, declare, plpgsql
Published: 2024-01-24 16:32:55

You can declare local variables with `:=`, `=` in [DECLARE clause](https://www.postgresql.org/docs/current/plpgsql-declarations.html) as shown below:

```sql
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](https://stackoverflow.com/questions/69519777/create-a-function-in-postgresql#77608956).

*[The doc](https://www.postgresql.org/docs/current/plpgsql-declarations.html) explains declarations.

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

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

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

```sql
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:

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

Next, you can declare aliases with [ALIAS FOR](https://www.postgresql.org/docs/current/plpgsql-declarations.html#PLPGSQL-DECLARATION-ALIAS) in `DECLARE` clause as shown below:

```sql
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:

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

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

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

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

```sql
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:

```sql
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](https://www.postgresql.org/docs/current/plpgsql-declarations.html#PLPGSQL-DECLARATION-TYPE) explains the local variables of table columns' types.

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

```sql
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:

```sql
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](https://www.postgresql.org/docs/current/plpgsql-declarations.html#PLPGSQL-DECLARATION-ROWTYPES) explains the local variable of a table row type.

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

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