---
title: "The PL/pgSQL declaration section"
url: "https://docs.yugabyte.com/stable/api/ysql/user-defined-subprograms-and-anon-blocks/language-plpgsql-subprograms/plpgsql-syntax-and-semantics/declaration-section/"
---

# The PL/pgSQL declaration section

Describes the syntax and semantics of the PL/pgSQL declaration section. [YSQL].

Every identifier that occurs within a PL/pgSQL block statement must be defined. Name resolution of such identifiers is attempted first in the most tightly-enclosing declaration section (if present), and if that fails, in the next most tightly-enclosing declaration section (if present)—and so on up through the outermost declaration section (if present). Only if all of these attempts fail are outer scopes tried. These scopes are first the list of formal arguments (for a subprogram) and then the schemas along the search path. See the section [Name resolution within user-defined subprograms and anonymous blocks](/stable/api/ysql/user-defined-subprograms-and-anon-blocks/name-resolution-in-subprograms/ "Name resolution within user-defined subprograms and anonymous blocks").

## Syntax

- [![Grammar Icon](/icons/file-lines.svg)Grammar](#grammar-1)
- [![Diagram Icon](/icons/diagram.svg)Diagram](#diagram-1)

```output.ebnf
plpgsql_declaration ::= plpgsql_regular_declaration
                        | plpgsql_bound_refcursor_declaration

plpgsql_regular_declaration ::= [ variable ] [ CONSTANT ] 
                                [ data_type ] [ NOT NULL ] 
                                [ := expression ] ;

plpgsql_bound_refcursor_declaration ::= plpgsql_bound_refcursor_name 
                                        [ [ NO ] SCROLL ]  CURSOR 
                                        [ ( plpgsql_cursor_arg 
                                          [ , ... ] ) ]  FOR subquery 
                                        ;

plpgsql_cursor_arg ::= formal_arg arg_type
```

#### plpgsql\_declaration

[plpgsql\_regular\_declaration](#plpgsql-regular-declaration)[plpgsql\_bound\_refcursor\_declaration](#plpgsql-bound-refcursor-declaration)

#### plpgsql\_regular\_declaration

[variable](/stable/api/ysql/syntax_resources/grammar_diagrams#variable)CONSTANT[data\_type](/stable/api/ysql/syntax_resources/grammar_diagrams#data-type)NOTNULL:=[expression](/stable/api/ysql/syntax_resources/grammar_diagrams#expression);

#### plpgsql\_bound\_refcursor\_declaration

[plpgsql\_bound\_refcursor\_name](/stable/api/ysql/syntax_resources/grammar_diagrams#plpgsql-bound-refcursor-name)NOSCROLLCURSOR(,[plpgsql\_cursor\_arg](#plpgsql-cursor-arg))FOR[subquery](/stable/api/ysql/syntax_resources/grammar_diagrams#subquery);

#### plpgsql\_cursor\_arg

[formal\_arg](/stable/api/ysql/syntax_resources/grammar_diagrams#formal-arg)[arg\_type](/stable/api/ysql/syntax_resources/grammar_diagrams#arg-type)

## Semantics for the "plpgsql\_regular\_declaration" rule

This rule governs the overwhelmingly common case.

Use any convenient sandbox database and a role that has no special attributes and that has *create* and *connect* on that database. Do this first:

```plpgsql
\set db <the sandbox database>
\set u  <the ordinary role>
```

Here's an example. It relies on the [**plpgsql\_open\_cursor\_stmt**](/stable/api/ysql/user-defined-subprograms-and-anon-blocks/language-plpgsql-subprograms/plpgsql-syntax-and-semantics/executable-section/basic-statements/cursor-manipulation/#plpgsql-open-cursor-stmt "plpgsql_open_cursor_stmt") and [**plpgsql\_fetch\_from\_cursor\_stmt**](/stable/api/ysql/user-defined-subprograms-and-anon-blocks/language-plpgsql-subprograms/plpgsql-syntax-and-semantics/executable-section/basic-statements/cursor-manipulation/#plpgsql-fetch-from-cursor-stmt "plpgsql_fetch_from_cursor_stmt") statements.

```plpgsql
\c :db :u
drop schema if exists s cascade;
create schema s;
create table s.t1(k int primary key, v text not null);
create table s.t2(k int primary key, v text not null);
insert into s.t1(k, v) values(1, 'cat');
insert into s.t2(k, v) values(1, 'dog');

create function s.f(x in boolean)
  returns table(z text)
  set search_path = pg_catalog, pg_temp
  language plpgsql
as $body$

-- The declare section of interest.
declare
  a  int;
  b  int not null := 17;
  c  constant text not null := case x
                                 when true then
                                   (select v from s.t1 where k = 1)
                                 else
                                   (select v from s.t2 where k = 1)
                               end;

  r  constant refcursor not null := 'cur';

  rec record;

-- The executable section (sanity check).
begin
  assert (a is null);

  z := 'b: '||b::text;                                                return next;
  z := 'c: '||c::text;                                                return next;

  open r for execute $$
      with c(source, v) as (
          select 'from t1', v from s.t1 where k = 1
          union all
          select 'from t2', v from s.t2 where k = 1
        )
      select source, v from c order by source
    $$;

  loop
    fetch r into rec;
    exit when not found;
    z := 'r: '|| rec.source||' | '||rec.v;                            return next;
  end loop;
  close r;

end;
$body$;

select s.f(true);
```

This is the result:

```output
 b: 17
 c: cat
 r: from t1 | cat
 r: from t2 | dog
```

Notice that this declaration:

```plpgsql
r  constant refcursor not null := 'cur';
```

establishes the variable *r* as a potential pointer to a [*cursor*](/stable/api/ysql/cursors/ "cursor") with the fixed name *cur*. A *refcursor* variable that is declared in this way (using the *plpgsql\_regular\_declaration* syntax) is referred to as an *unbound refcursor variable*. Here, *unbound* captures the idea that the *open* executable statement can use an arbitrary [*subquery*](/stable/api/ysql/syntax_resources/grammar_diagrams/#subquery "subquery") that defines a result set whose shape emerges at run-time, thus:

```plpgsql
open r for execute $$
    with c(source, v) as (
        select 'from t1', v from s.t1 where k = 1
        union all
        select 'from t2', v from s.t2 where k = 1
      )
    select source, v from c order by source
  $$;
```

This has the same effect as this top-level [*declare*](/stable/api/ysql/the-sql-language/statements/dml_declare/ "declare") SQL statement:

```plpgsql
declare cur no scroll cursor without hold for
  with c(source, v) as (
      select 'from t1', v from s.t1 where k = 1
      union all
      select 'from t2', v from s.t2 where k = 1
    )
select source, v from c order by source;
```

Notice that the target for the *fetch* statement, *rec*, is declared as a *record* so that it can accommodate a result row of any shape. It is the programmer's responsibility to know what the result's column names and data types will be so that the fields (in this example *rec.source* and *rec.v*) can be referenced correctly.

## Semantics for the "plpgsql\_bound\_refcursor\_declaration" rule

Make sure that you have read the section 'Cursors' before reading this subsection.

The [Cursors](/stable/api/ysql/cursors/ "Cursors") section is a direct child of the major section [Yugabyte Structured Query Language (YSQL)](/stable/api/ysql/ "Yugabyte Structured Query Language (YSQL)") and, as such, is a peer of the [User-defined subprograms and anonymous blocks](/stable/api/ysql/user-defined-subprograms-and-anon-blocks/ "User-defined subprograms and anonymous blocks") section. This reflects the fact that cursor functionality is first and foremost a SQL feature—just as, for example, *select*, *insert*, *update*, and *delete* are.

Here is an example. It uses the [**"Query for loop" with a bound refcursor variable**](/stable/api/ysql/user-defined-subprograms-and-anon-blocks/language-plpgsql-subprograms/plpgsql-syntax-and-semantics/executable-section/compound-statements/loop-exit-continue/query-for-loop/#query-for-loop-with-a-bound-refcursor-variable '"Query for loop" with a bound refcursor variable').

```plpgsql
create table s.t3(k int primary key, v text not null);
insert into s.t3(k, v) select g.s, g.s*10 from generate_series(1, 10) as g(s);

drop function if exists s.f() cascade;

create function s.f()
  returns table(z text)
  set search_path = pg_catalog, pg_temp
  language plpgsql
as $body$

-- The declare section of interest.
declare
  r no scroll cursor(lo int, hi int) for
  select v from s.t3 where k between lo and hi order by k;

-- The executable section (sanity check).
begin
  assert (pg_typeof(r)::text = 'refcursor');

  for rec in r(lo := 7, hi := 9) loop
    z := rec.v; return next;
  end loop;
end;
$body$;

select s.f();
```

This is the result:

```output
 f
----
 70
 80
 90
```

Here, the *refcursor* variable *r* (declared using the *plpgsql\_bound\_refcursor\_declaration* syntax) is referred to as a *bound refcursor variable* because its *subquery* is irrevocably determined at declaration time. This allows it to be used in a *cursor for loop*. A *bound refcursor variable* can also be used as the argument of an explicit *open* statement. But here you cannot (re)specify the already-specified *subquery*.

Notice that the way you list the optional formal arguments in the declaration of a *bound refcursor variable* differs from how this is done for a user-defined subprogram in that you *must* name each argument and you cannot provide an *in/out* mode or a default value. This means that all the arguments are mandatory and that you must provide an actual argument value for each: with a *cursor for loop* as part of its *in* clause; or in the explicit *open* statement.

## Syntax errors and semantics errors in the declare section

A syntax error in the declaration section, when the PL/pgSQL block statement is the argument of a [*do*](/stable/api/ysql/syntax_resources/grammar_diagrams/#do "do") statement, prevents the attempt to execute it. And a syntax error in the declaration section, when the PL/pgSQL block statement is the argument of a [*create function*](/stable/api/ysql/syntax_resources/grammar_diagrams/#create-function "create function") or [*create procedure*](/stable/api/ysql/syntax_resources/grammar_diagrams/#create-procedure "create procedure") statement (more carefully stated, when it's an [unalterable\_fn\_attribute](/stable/api/ysql/syntax_resources/grammar_diagrams/#unalterable-fn-attribute "unalterable_fn_attribute") or an [unalterable\_proc\_attribute](/stable/api/ysql/syntax_resources/grammar_diagrams/#unalterable-proc-attribute "unalterable_proc_attribute")), prevents the to-be-created subprogram from being recorded in the catalog.

A semantic error in the declaration section shows up as a run-time error. Notice that the exception as which the error manifests within PL/pgSQL cannot be handled in the exception section of the [*plpgsql\_block\_stmt*](/stable/api/ysql/syntax_resources/grammar_diagrams/#plpgsql-block-stmt "plpgsql_block_stmt") that the declaration section introduces. Rather, the exception can be handled only in the exception section of an enclosing *plpgsql\_block\_stmt*.

### Syntax errors

Try this:

```plpgsql
drop schema if exists s cascade;
create schema s;

create procedure s.p()
  set search_path = pg_catalog, pg_temp
  language plpgsql
as $body$
declare
  a  int := 1  2;
begin
  assert a = 1;
end;
$body$;
```

The attempt causes this error:

```output
42601: syntax error at or near "2"
```

and *call s.p()* causes the error *42883: procedure s.p() does not exist*.

Now try this. (The syntax rules simply insist that no variable can be declared more than once.)

```plpgsql
drop schema if exists s cascade;
create schema s;

create procedure s.p()
  set search_path = pg_catalog, pg_temp
  language plpgsql
as $body$
declare
  a  int;
  a  text;
begin

end;
$body$;
```

The attempt causes this error:

```pitput
42601: duplicate declaration at or near "a"
```

and, again, *call s.p()* shows that it doesn't exist.

### Semantic errors

Here's a trivial example:

```plpgsql
drop schema if exists s cascade;
create schema s;

create procedure s.p()
  set search_path = pg_catalog, pg_temp
  language plpgsql
as $body$
declare
  a  constant numeric not null := swrt(4.0);
begin
  assert (a = 2.0);
end;
$body$;
```

No errors are reported—and *s.p()* is recorded in the catalog. But the *call s.p()* attempt causes this error, attributed to the line that declares *a*:

```output
42883: function swrt(numeric) does not exist
```

Now try a more subtle example:

```plpgsql
drop schema if exists s cascade;
create schema s;

create procedure s.p()
  set search_path = pg_catalog, pg_temp
  language plpgsql
as $body$
declare
  a  constant int not null := 17;
  c           int not null := a + b;
  b  constant int not null := 42;
begin
  assert (c = 17 + 42);
end;
$body$;
```

No errors are reported—and *s.p()* is recorded in the catalog. Notice that when *b* is referenced in the expression that initializes *c*, it has not yet been declared. This happens on the next line. But forward references are not allowed—and such an error is considered to be a semantic error and is therefore not detected until *call s.p()* is attempted. The attempt causes this error, attributed to the line that declares *c*:

```output
42703: column "b" does not exist
```