Try a preview of AI-powered search below!

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.

Syntax

• Grammar
• Diagram

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

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:

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

Here's an example. It relies on the plpgsql_open_cursor_stmt and plpgsql_fetch_from_cursor_stmt statements.

\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:

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

Notice that this declaration:

r  constant refcursor not null := 'cur';

establishes the variable r as a potential pointer to a 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 that defines a result set whose shape emerges at run-time, thus:

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 SQL statement:

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

Here is an example. It uses the "Query for loop" with a bound refcursor variable.

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:

 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 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 or create procedure statement (more carefully stated, when it's an unalterable_fn_attribute or an 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 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:

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:

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.)

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:

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:

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:

42883: function swrt(numeric) does not exist

Now try a more subtle example:

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:

42703: column "b" does not exist