text: --
  -- Tests for PL/pgSQL handling of composite (record) variables
  --
  
  create type two_int4s as (f1 int4, f2 int4);
  create type two_int8s as (q1 int8, q2 int8);
  
  -- base-case return of a composite type
  create function retc(int) returns two_int8s language plpgsql as
  $$ begin return row($1,1)::two_int8s; end $$;
  select retc(42);
  
  -- ok to return a matching record type
  create or replace function retc(int) returns two_int8s language plpgsql as
  $$ begin return row($1::int8, 1::int8); end $$;
  select retc(42);
  
  -- we don't currently support implicit casting
  create or replace function retc(int) returns two_int8s language plpgsql as
  $$ begin return row($1,1); end $$;
  select retc(42);
  
  -- nor extra columns
  create or replace function retc(int) returns two_int8s language plpgsql as
  $$ begin return row($1::int8, 1::int8, 42); end $$;
  select retc(42);
  
  -- same cases with an intermediate "record" variable
  create or replace function retc(int) returns two_int8s language plpgsql as
  $$ declare r record; begin r := row($1::int8, 1::int8); return r; end $$;
  select retc(42);
  
  create or replace function retc(int) returns two_int8s language plpgsql as
  $$ declare r record; begin r := row($1,1); return r; end $$;
  select retc(42);
  
  create or replace function retc(int) returns two_int8s language plpgsql as
  $$ declare r record; begin r := row($1::int8, 1::int8, 42); return r; end $$;
  select retc(42);
  
  -- but, for mostly historical reasons, we do convert when assigning
  -- to a named-composite-type variable
  create or replace function retc(int) returns two_int8s language plpgsql as
  $$ declare r two_int8s; begin r := row($1::int8, 1::int8, 42); return r; end $$;
  select retc(42);
  
  do $$ declare c two_int8s;
  begin c := row(1,2); raise notice 'c = %', c; end$$;
  
  do $$ declare c two_int8s;
  begin for c in select 1,2 loop raise notice 'c = %', c; end loop; end$$;
  
  do $$ declare c4 two_int4s; c8 two_int8s;
  begin
    c8 := row(1,2);
    c4 := c8;
    c8 := c4;
    raise notice 'c4 = %', c4;
    raise notice 'c8 = %', c8;
  end$$;
  
  -- check passing composite result to another function
  create function getq1(two_int8s) returns int8 language plpgsql as $$
  declare r two_int8s; begin r := $1; return r.q1; end $$;
  
  select getq1(retc(344));
  select getq1(row(1,2));
  
  do $$
  declare r1 two_int8s; r2 record; x int8;
  begin
    r1 := retc(345);
    perform getq1(r1);
    x := getq1(r1);
    raise notice 'x = %', x;
    r2 := retc(346);
    perform getq1(r2);
    x := getq1(r2);
    raise notice 'x = %', x;
  end$$;
  
  -- check assignments of composites
  do $$
  declare r1 two_int8s; r2 two_int8s; r3 record; r4 record;
  begin
    r1 := row(1,2);
    raise notice 'r1 = %', r1;
    r1 := r1;  -- shouldn't do anything
    raise notice 'r1 = %', r1;
    r2 := r1;
    raise notice 'r1 = %', r1;
    raise notice 'r2 = %', r2;
    r2.q2 = r1.q1 + 3;  -- check that r2 has distinct storage
    raise notice 'r1 = %', r1;
    raise notice 'r2 = %', r2;
    r1 := null;
    raise notice 'r1 = %', r1;
    raise notice 'r2 = %', r2;
    r1 := row(7,11)::two_int8s;
    r2 := r1;
    raise notice 'r1 = %', r1;
    raise notice 'r2 = %', r2;
    r3 := row(1,2);
    r4 := r3;
    raise notice 'r3 = %', r3;
    raise notice 'r4 = %', r4;
    r4.f1 := r4.f1 + 3;  -- check that r4 has distinct storage
    raise notice 'r3 = %', r3;
    raise notice 'r4 = %', r4;
    r1 := r3;
    raise notice 'r1 = %', r1;
    r4 := r1;
    raise notice 'r4 = %', r4;
    r4.q2 := r4.q2 + 1;  -- r4's field names have changed
    raise notice 'r4 = %', r4;
  end$$;
  
  -- fields of named-type vars read as null if uninitialized
  do $$
  declare r1 two_int8s;
  begin
    raise notice 'r1 = %', r1;
    raise notice 'r1.q1 = %', r1.q1;
    raise notice 'r1.q2 = %', r1.q2;
    raise notice 'r1 = %', r1;
  end$$;
  
  do $$
  declare r1 two_int8s;
  begin
    raise notice 'r1.q1 = %', r1.q1;
    raise notice 'r1.q2 = %', r1.q2;
    raise notice 'r1 = %', r1;
    raise notice 'r1.nosuchfield = %', r1.nosuchfield;
  end$$;
  
  -- records, not so much
  do $$
  declare r1 record;
  begin
    raise notice 'r1 = %', r1;
    raise notice 'r1.f1 = %', r1.f1;
    raise notice 'r1.f2 = %', r1.f2;
    raise notice 'r1 = %', r1;
  end$$;
  
  -- but OK if you assign first
  do $$
  declare r1 record;
  begin
    raise notice 'r1 = %', r1;
    r1 := row(1,2);
    raise notice 'r1.f1 = %', r1.f1;
    raise notice 'r1.f2 = %', r1.f2;
    raise notice 'r1 = %', r1;
    raise notice 'r1.nosuchfield = %', r1.nosuchfield;
  end$$;
  
  -- check repeated assignments to composite fields
  create table some_table (id int, data text);
  
  do $$
  declare r some_table;
  begin
    r := (23, 'skidoo');
    for i in 1 .. 10 loop
      r.id := r.id + i;
      r.data := r.data || ' ' || i;
    end loop;
    raise notice 'r = %', r;
  end$$;
  
  -- check behavior of function declared to return "record"
  
  create function returnsrecord(int) returns record language plpgsql as
  $$ begin return row($1,$1+1); end $$;
  
  select returnsrecord(42);
  select * from returnsrecord(42) as r(x int, y int);
  select * from returnsrecord(42) as r(x int, y int, z int);  -- fail
  select * from returnsrecord(42) as r(x int, y bigint);  -- fail
  
  -- same with an intermediate record variable
  create or replace function returnsrecord(int) returns record language plpgsql as
  $$ declare r record; begin r := row($1,$1+1); return r; end $$;
  
  select returnsrecord(42);
  select * from returnsrecord(42) as r(x int, y int);
  select * from returnsrecord(42) as r(x int, y int, z int);  -- fail
  select * from returnsrecord(42) as r(x int, y bigint);  -- fail
  
  -- should work the same with a missing column in the actual result value
  create table has_hole(f1 int, f2 int, f3 int);
  alter table has_hole drop column f2;
  
  create or replace function returnsrecord(int) returns record language plpgsql as
  $$ begin return row($1,$1+1)::has_hole; end $$;
  
  select returnsrecord(42);
  select * from returnsrecord(42) as r(x int, y int);
  select * from returnsrecord(42) as r(x int, y int, z int);  -- fail
  select * from returnsrecord(42) as r(x int, y bigint);  -- fail
  
  -- same with an intermediate record variable
  create or replace function returnsrecord(int) returns record language plpgsql as
  $$ declare r record; begin r := row($1,$1+1)::has_hole; return r; end $$;
  
  select returnsrecord(42);
  select * from returnsrecord(42) as r(x int, y int);
  select * from returnsrecord(42) as r(x int, y int, z int);  -- fail
  select * from returnsrecord(42) as r(x int, y bigint);  -- fail
  
  -- check access to a field of an argument declared "record"
  create function getf1(x record) returns int language plpgsql as
  $$ begin return x.f1; end $$;
  select getf1(1);
  select getf1(row(1,2));
  -- a CLOBBER_CACHE_ALWAYS build will report this error with a different
  -- context stack than other builds, so suppress context output
  \set SHOW_CONTEXT never
  select getf1(row(1,2)::two_int8s);
  \set SHOW_CONTEXT errors
  select getf1(row(1,2));
  
  -- check behavior when assignment to FOR-loop variable requires coercion
  do $$
  declare r two_int8s;
  begin
    for r in select i, i+1 from generate_series(1,4) i
    loop
      raise notice 'r = %', r;
    end loop;
  end$$;
  
  -- check behavior when returning setof composite
  create function returnssetofholes() returns setof has_hole language plpgsql as
  $$
  declare r record;
    h has_hole;
  begin
    return next h;
    r := (1,2);
    h := (3,4);
    return next r;
    return next h;
    return next row(5,6);
    return next row(7,8)::has_hole;
  end$$;
  select returnssetofholes();
  
  create or replace function returnssetofholes() returns setof has_hole language plpgsql as
  $$
  declare r record;
  begin
    return next r;  -- fails, not assigned yet
  end$$;
  select returnssetofholes();
  
  create or replace function returnssetofholes() returns setof has_hole language plpgsql as
  $$
  begin
    return next row(1,2,3);  -- fails
  end$$;
  select returnssetofholes();
  
  -- check behavior with changes of a named rowtype
  create table mutable(f1 int, f2 text);
  
  create function sillyaddone(int) returns int language plpgsql as
  $$ declare r mutable; begin r.f1 := $1; return r.f1 + 1; end $$;
  select sillyaddone(42);
  
  alter table mutable drop column f1;
  alter table mutable add column f1 float8;
  
  -- currently, this fails due to cached plan for "r.f1 + 1" expression
  -- (but we can't actually show that, because a CLOBBER_CACHE_ALWAYS build
  -- will succeed)
  -- select sillyaddone(42);
  
  -- but it's OK if we force plan rebuilding
  discard plans;
  select sillyaddone(42);
  
  alter table mutable drop column f1;
  select sillyaddone(42);  -- fail
  
  create function getf3(x mutable) returns int language plpgsql as
  $$ begin return x.f3; end $$;
  select getf3(null::mutable);  -- doesn't work yet
  alter table mutable add column f3 int;
  select getf3(null::mutable);  -- now it works
  alter table mutable drop column f3;
  -- a CLOBBER_CACHE_ALWAYS build will report this error with a different
  -- context stack than other builds, so suppress context output
  \set SHOW_CONTEXT never
  select getf3(null::mutable);  -- fails again
  \set SHOW_CONTEXT errors
  
  -- check access to system columns in a record variable
  
  create function sillytrig() returns trigger language plpgsql as
  $$begin
    raise notice 'old.ctid = %', old.ctid;
    raise notice 'old.tableoid = %', old.tableoid::regclass;
    return new;
  end$$;
  
  create trigger mutable_trig before update on mutable for each row
  execute procedure sillytrig();
  
  insert into mutable values ('foo'), ('bar');
  update mutable set f2 = f2 || ' baz';
  table mutable;
  
  -- check returning a composite datum from a trigger
  
  create or replace function sillytrig() returns trigger language plpgsql as
  $$begin
    return row(new.*);
  end$$;
  
  update mutable set f2 = f2 || ' baz';
  table mutable;
  
  create or replace function sillytrig() returns trigger language plpgsql as
  $$declare r record;
  begin
    r := row(new.*);
    return r;
  end$$;
  
  update mutable set f2 = f2 || ' baz';
  table mutable;
  
  --
  -- Domains of composite
  --
  
  create domain ordered_int8s as two_int8s check((value).q1 <= (value).q2);
  
  create function read_ordered_int8s(p ordered_int8s) returns int8 as $$
  begin return p.q1 + p.q2; end
  $$ language plpgsql;
  
  select read_ordered_int8s(row(1, 2));
  select read_ordered_int8s(row(2, 1));  -- fail
  
  create function build_ordered_int8s(i int8, j int8) returns ordered_int8s as $$
  begin return row(i,j); end
  $$ language plpgsql;
  
  select build_ordered_int8s(1,2);
  select build_ordered_int8s(2,1);  -- fail
  
  create function build_ordered_int8s_2(i int8, j int8) returns ordered_int8s as $$
  declare r record; begin r := row(i,j); return r; end
  $$ language plpgsql;
  
  select build_ordered_int8s_2(1,2);
  select build_ordered_int8s_2(2,1);  -- fail
  
  create function build_ordered_int8s_3(i int8, j int8) returns ordered_int8s as $$
  declare r two_int8s; begin r := row(i,j); return r; end
  $$ language plpgsql;
  
  select build_ordered_int8s_3(1,2);
  select build_ordered_int8s_3(2,1);  -- fail
  
  create function build_ordered_int8s_4(i int8, j int8) returns ordered_int8s as $$
  declare r ordered_int8s; begin r := row(i,j); return r; end
  $$ language plpgsql;
  
  select build_ordered_int8s_4(1,2);
  select build_ordered_int8s_4(2,1);  -- fail
  
  create function build_ordered_int8s_a(i int8, j int8) returns ordered_int8s[] as $$
  begin return array[row(i,j), row(i,j+1)]; end
  $$ language plpgsql;
  
  select build_ordered_int8s_a(1,2);
  select build_ordered_int8s_a(2,1);  -- fail
  
  -- check field assignment
  do $$
  declare r ordered_int8s;
  begin
    r.q1 := null;
    r.q2 := 43;
    r.q1 := 42;
    r.q2 := 41;  -- fail
  end$$;
  
  -- check whole-row assignment
  do $$
  declare r ordered_int8s;
  begin
    r := null;
    r := row(null,null);
    r := row(1,2);
    r := row(2,1);  -- fail
  end$$;
  
  -- check assignment in for-loop
  do $$
  declare r ordered_int8s;
  begin
    for r in values (1,2),(3,4),(6,5) loop
      raise notice 'r = %', r;
    end loop;
  end$$;
  
  -- check behavior with toastable fields, too
  
  create type two_texts as (f1 text, f2 text);
  create domain ordered_texts as two_texts check((value).f1 <= (value).f2);
  
  create table sometable (id int, a text, b text);
  -- b should be compressed, but in-line
  insert into sometable values (1, 'a', repeat('ffoob',1000));
  -- this b should be out-of-line
  insert into sometable values (2, 'a', repeat('ffoob',100000));
  -- this pair should fail the domain check
  insert into sometable values (3, 'z', repeat('ffoob',100000));
  
  do $$
  declare d ordered_texts;
  begin
    for d in select a, b from sometable loop
      raise notice 'succeeded at "%"', d.f1;
    end loop;
  end$$;
  
  do $$
  declare r record; d ordered_texts;
  begin
    for r in select * from sometable loop
      raise notice 'processing row %', r.id;
      d := row(r.a, r.b);
    end loop;
  end$$;
  
  do $$
  declare r record; d ordered_texts;
  begin
    for r in select * from sometable loop
      raise notice 'processing row %', r.id;
      d := null;
      d.f1 := r.a;
      d.f2 := r.b;
    end loop;
  end$$;
  