-- test create type table of
-- check compatibility --
show sql_compatibility; -- expect A --
sql_compatibility
-------------------
A
(1 row)
-- create new schema --
drop schema if exists plpgsql_table;
NOTICE: schema "plpgsql_table" does not exist, skipping
create schema plpgsql_table;
set current_schema = plpgsql_table;
set behavior_compat_options='allow_procedure_compile_check';
create type s_type as (
id integer,
name varchar,
addr text
);
create type typeA as table of s_type;
create type typeB as table of s_type.id%type;
NOTICE: type reference s_type.id%TYPE converted to integer
create type typeC as table of s_type.name%type;
NOTICE: type reference s_type.name%TYPE converted to character varying
create type typeD as table of varchar(100);
-- test alter
alter type typeA drop ATTRIBUTE s_type;
ERROR: table type does not support alter.
alter type typeA ADD ATTRIBUTE a int;
ERROR: table type does not support alter.
create type typeC2 as table of s_type.name%type;
NOTICE: type reference s_type.name%TYPE converted to character varying
alter type typeC2 RENAME TO typeC3;
create or replace procedure tableof_alter()
is
a typeC3;
begin
a(1) = (1, 'zhangsan', 'shanghai');
RAISE INFO 'call a(1): %' ,a(1);
end;
/
call tableof_alter();
INFO: call a(1): (1,zhangsan,shanghai)
tableof_alter
---------------
(1 row)
-- test is
create type s_type_1 is (
id integer,
name varchar,
addr text
);
create type typeA1 is table of s_type_1;
create type typeB1 is table of s_type_1.id%type;
NOTICE: type reference s_type_1.id%TYPE converted to integer
create type typeC1 is table of s_type_1.name%type;
NOTICE: type reference s_type_1.name%TYPE converted to character varying
create type typeD1 is table of varchar(100);
create or replace function tableof_5_1()
return typeA1 as
a typeA1;
b typeA1;
begin
a(1) = (1, 'lisi', 'beijing');
b = a;
b(2) = (2, 'zahngwu', 'chengdu');
RAISE INFO 'a:%' ,a;
return b;
end;
/
select tableof_5_1();
INFO: a:{"(1,lisi,beijing)"}
CONTEXT: referenced column: tableof_5_1
tableof_5_1
--------------------------------------------
{"(1,lisi,beijing)","(2,zahngwu,chengdu)"}
(1 row)
drop procedure tableof_5_1;
-- test as
create or replace procedure tableof_12_1
is
TYPE SalTabTyp as TABLE OF varchar(10) index by BINARY_INTEGER;
aa SalTabTyp;
begin
aa(0) = 1;
aa(1) = 2;
RAISE INFO '%' ,aa(0);
RAISE INFO '%' ,aa(1);
end;
/
call tableof_12_1();
INFO: 1
INFO: 2
tableof_12_1
--------------
(1 row)
create or replace procedure tableof_12_2
is
TYPE SalTabTyp as TABLE OF varchar(10) index by BINARY_INTEGER;
aa SalTabTyp;
begin
aa(-1) = 1;
aa(2) = 2;
RAISE INFO '%' ,aa(0);
RAISE INFO '%' ,aa(1);
RAISE INFO '%', aa.count;
RAISE INFO '%', aa;
end;
/
call tableof_12_2();
INFO: <NULL>
INFO: <NULL>
INFO: 2
INFO: {1,2}
tableof_12_2
--------------
(1 row)
drop procedure tableof_12_1;
drop procedure tableof_12_2;
drop type s_type_1;
ERROR: cannot drop type s_type_1 because other objects depend on it
DETAIL: type _s_type_1[] depends on type s_type_1[]
HINT: Use DROP ... CASCADE to drop the dependent objects too.
drop type typeA1;
drop type typeB1;
drop type typeC1;
drop type typeD1;
-- test table of nest table of error
create type typeF as table of typeD;
ERROR: table type does not support nested table.
-- don't support alter attr
alter type typeA ADD ATTRIBUTE a int;
ERROR: table type does not support alter.
-- test in paramter
create or replace procedure tableof_1(a typeA)
is
begin
RAISE INFO 'a(1): %' ,a(1);
a(1) = (2, 'lisi', 'beijing');
a(2) = (3, 'zahngwu', 'chengdu');
end;
/
create or replace procedure tableof_2()
is
a typeA;
begin
a(1) = (1, 'zhangsan', 'shanghai');
RAISE INFO 'before call a(1): %' ,a(1);
perform tableof_1(a);
RAISE INFO 'after call a(2): %' ,a(2);
end;
/
call tableof_2();
INFO: before call a(1): (1,zhangsan,shanghai)
INFO: a(1): (1,zhangsan,shanghai)
CONTEXT: referenced column: tableof_1
SQL statement "SELECT tableof_1(a)"
PL/pgSQL function tableof_2() line 5 at PERFORM
INFO: after call a(2): <NULL>
tableof_2
-----------
(1 row)
-- don't support create type = ()
create or replace procedure tableof_3
is
aa typeA = typeA();
begin
RAISE INFO '%' ,aa;
end;
/
call tableof_3();
INFO: {}
tableof_3
-----------
(1 row)
-- test return
create or replace function tableof_4()
return typeA as
a typeA;
begin
a(1) = (1, 'lisi', 'beijing');
return a;
end;
/
select tableof_4();
tableof_4
----------------------
{"(1,lisi,beijing)"}
(1 row)
create or replace function tableof_4()
return typeA as
a typeA;
begin
a(1) = (1, 'lisi', 'beijing');
return a;
end;
/
select tableof_4();
tableof_4
----------------------
{"(1,lisi,beijing)"}
(1 row)
create or replace function tableof_5()
return typeA as
a typeA;
b typeA;
begin
a(1) = (1, 'lisi', 'beijing');
b = a;
b(2) = (2, 'zahngwu', 'chengdu');
RAISE INFO 'a:%' ,a;
return b;
end;
/
select tableof_5();
INFO: a:{"(1,lisi,beijing)"}
CONTEXT: referenced column: tableof_5
tableof_5
--------------------------------------------
{"(1,lisi,beijing)","(2,zahngwu,chengdu)"}
(1 row)
-- test cast
create or replace function tableof_6()
return typeC as
a typeA;
b typeC;
begin
a(1) = (1, 'lisi', 'beijing');
b = a;
b(2) = (2, 'zahngwu', 'chengdu');
RAISE INFO 'a:%' ,a;
return b;
end;
/
select tableof_6();
INFO: a:{"(1,lisi,beijing)"}
CONTEXT: referenced column: tableof_6
tableof_6
--------------------------------------------
{"(1,lisi,beijing)","(2,zahngwu,chengdu)"}
(1 row)
--test return wrong type
create or replace function tableof_7()
return typeB as
a typeA;
b typeC;
begin
a(1) = (1, 'lisi', 'beijing');
b = a;
b(2) = (2, 'zahngwu', 'chengdu');
RAISE INFO 'a:%' ,a;
return b;
end;
/
select tableof_7();
INFO: a:{"(1,lisi,beijing)"}
CONTEXT: referenced column: tableof_7
ERROR: invalid input syntax for integer: "(1,lisi,beijing)"
CONTEXT: PL/pgSQL function tableof_7() while casting return value to function's return type
referenced column: tableof_7
-- add one column from s_type
create type s_type_extend as (
id integer,
name varchar,
addr text,
comment varchar
);
create type typeA_ext as table of s_type_extend;
create or replace function tableof_8()
return typeA_ext as
a typeA;
b typeA_ext;
begin
a(1) = (1, 'lisi', 'beijing');
b = a;
b(2) = (2, 'zahngwu', 'chengdu','good');
RAISE INFO 'a:%' ,a;
return b;
end;
/
select tableof_8();
ERROR: malformed record literal: "(1,lisi,beijing)"
DETAIL: Too few columns.
CONTEXT: PL/pgSQL function tableof_8() line 5 at assignment
referenced column: tableof_8
-- test return index
create or replace function tableof_9()
return typeA as
a typeA;
begin
a(-1) = (1, 'lisi', 'beijing');
a(2) = (2, 'zahngwu', 'chengdu');
return a;
end;
/
select tableof_9();
tableof_9
-------------------------------------------------------------
[-1:2]={"(1,lisi,beijing)",NULL,NULL,"(2,zahngwu,chengdu)"}
(1 row)
create or replace procedure tableof_10()
as
a typeA;
begin
a = tableof_9();
RAISE INFO 'a(-1):%' ,a(-1);
RAISE INFO 'a(0):%' ,a(0);
RAISE INFO 'a(2):%' ,a(2).id;
end;
/
call tableof_10();
INFO: a(-1):(1,lisi,beijing)
INFO: a(0):<NULL>
INFO: a(2):2
tableof_10
------------
(1 row)
create or replace procedure tableof_11()
as
a typeA;
begin
a = tableof_9();
RAISE INFO 'a(-1):%' ,a(-1);
end;
/
call tableof_11();
INFO: a(-1):(1,lisi,beijing)
tableof_11
------------
(1 row)
-- test index by
create or replace procedure tableof_12
is
TYPE SalTabTyp is TABLE OF varchar(10) index by BINARY_INTEGER;
aa SalTabTyp;
begin
aa('aa') = 1;
aa('bb') = 2;
RAISE INFO '%' ,aa('aa');
RAISE INFO '%' ,aa('bb');
end;
/
call tableof_12();
ERROR: invalid input syntax for integer: "aa"
CONTEXT: PL/pgSQL function tableof_12() line 4 at assignment
create or replace procedure tableof_13
is
TYPE SalTabTyp is TABLE OF integer index by varchar(10);
aa SalTabTyp;
begin
aa('aa') = 1;
aa('bb') = 2;
RAISE INFO '%' ,aa(0);
RAISE INFO '%' ,aa('bb');
end;
/
call tableof_13();
INFO: <NULL>
INFO: 2
tableof_13
------------
(1 row)
create or replace procedure tableof_14
is
TYPE SalTabTyp is TABLE OF integer index by varchar(10);
aa SalTabTyp;
b varchar(10);
begin
aa('a') = 1;
b = 'aa';
aa(b) = 2;
RAISE INFO '%' ,aa('a');
RAISE INFO '%' ,aa('aa');
RAISE INFO '%' ,aa(b);
end;
/
call tableof_14();
INFO: 1
INFO: 2
INFO: 2
tableof_14
------------
(1 row)
create or replace procedure tableof_15
is
TYPE SalTabTyp is TABLE OF varchar(10) index by date;
aa SalTabTyp;
begin
end;
/
ERROR: unsupported table index type
CONTEXT: compilation of PL/pgSQL function "tableof_15" near line 1
create or replace procedure tableof_15
is
TYPE SalTabTyp is TABLE OF varchar(10) index by text;
aa SalTabTyp;
begin
end;
/
ERROR: unsupported table index type
CONTEXT: compilation of PL/pgSQL function "tableof_15" near line 1
-- test table = table
create or replace procedure tableof_16
is
TYPE SalTabTyp is TABLE OF varchar(10) index by BINARY_INTEGER;
aa SalTabTyp;
bb SalTabTyp;
begin
aa(-1) = 'b';
aa(1) = 'a';
RAISE INFO '%' ,aa(-1);
bb = aa;
RAISE INFO '%' ,bb(-1);
bb(8) = 'g';
RAISE INFO '%' ,bb(8);
RAISE INFO '%' ,aa(8);
end;
/
call tableof_16();
INFO: b
INFO: b
INFO: g
INFO: <NULL>
tableof_16
------------
(1 row)
-- test define
create or replace procedure tableof_17
is
TYPE SalTabTyp is TABLE OF s_type%rowtype index by varchar(10);
aa SalTabTyp;
begin
aa('a') = (1, 'zhangsan', 'shanghai');
aa('b') = (2, 'lisi', 'beijing');
RAISE INFO '%' ,aa('a').id;
RAISE INFO '%' ,aa('b');
end;
/
call tableof_17();
INFO: 1
INFO: (2,lisi,beijing)
tableof_17
------------
(1 row)
create or replace procedure tableof_18
is
declare
TYPE SalTabTyp is TABLE OF s_type.id%type index by varchar(10);
aa SalTabTyp;
begin
aa('a') = 1;
aa('b') = 2;
aa = NULL;
RAISE INFO '%' ,aa('a');
RAISE INFO '%' ,aa('b');
aa('a') = 1;
aa('C') = 2;
aa('b') = 3;
RAISE INFO '%' ,aa;
end;
/
call tableof_18();
INFO: <NULL>
INFO: <NULL>
INFO: {1,2,3}
tableof_18
------------
(1 row)
-- test not null gram
create or replace procedure tableof_19
is
TYPE SalTabTyp is TABLE OF s_type%rowtype not null index by varchar(10);
aa SalTabTyp;
begin
aa('a') = (1, 'zhangsan', 'shanghai');
RAISE INFO '%' ,aa('a');
end;
/
call tableof_19();
INFO: (1,zhangsan,shanghai)
tableof_19
------------
(1 row)
-- test assign one attr
create or replace procedure tableof_20
is
TYPE SalTabTyp is TABLE OF s_type%rowtype not null index by varchar(10);
aa SalTabTyp;
begin
aa('a') = (1, 'zhangsan', 'shanghai');
aa('a').id = 1;
end;
/
call tableof_20();
tableof_20
------------
(1 row)
create type info as (name varchar2(50), age int, address varchar2(20), salary float(2));
create type customer as (id number(10), c_info info);
create table customers (id number(10), c_info info);
insert into customers (id, c_info) values (1, ('Vera' ,32, 'Paris', 22999.00));
insert into customers (id, c_info) values (2, ('Zera' ,25, 'London', 5999.00));
insert into customers (id, c_info) values (3, ('Alice' ,22, 'Bangkok', 9800.98));
insert into customers (id, c_info) values (4, ('Jim' ,26, 'Dubai', 18700.00));
insert into customers (id, c_info) values (5, ('Kevin' ,28, 'Singapore', 18999.00));
insert into customers (id, c_info) values (6, ('Gauss' ,42, 'Beijing', 32999.00));
-- test curosor fetch into
create or replace procedure tableof_21
as
declare
TYPE id_1 is TABLE OF customer.id%type index by varchar(10);
TYPE c_info_1 is TABLE OF customers.c_info%type index by varchar(10);
CURSOR C1 IS SELECT id FROM customers order by id;
CURSOR C2 IS SELECT c_info FROM customers order by id;
info_a c_info_1:=c_info_1();
id_a id_1:=id_1();
begin
OPEN C1;
OPEN C2;
FETCH C1 into id_a(2);
FETCH C2 into info_a(2);
FETCH C1 into id_a(3);
FETCH C2 into info_a(3);
CLOSE C1;
CLOSE C2;
RAISE INFO '%', id_a;
RAISE INFO '%', info_a;
end;
/
call tableof_21();
INFO: {1,2}
INFO: {"(Vera,32,Paris,22999)","(Zera,25,London,5999)"}
tableof_21
------------
(1 row)
-- test select into
create or replace procedure tableof_22
as
declare
TYPE id_1 is TABLE OF customer.id%type index by varchar(10);
TYPE c_info_1 is TABLE OF customers.c_info%type index by varchar(10);
info_a c_info_1:=c_info_1();
id_a id_1:=id_1();
begin
select id into id_a(2) from customers where id = 3;
select c_info into info_a(2) from customers where id = 3;
select id into id_a(3) from customers where id = 4;
select c_info into info_a(3) from customers where id = 4;
RAISE INFO '%', id_a(2);
RAISE INFO '%', info_a(3).age;
end;
/
call tableof_22();
INFO: 3
INFO: 26
tableof_22
------------
(1 row)
-- test curosor for
create or replace procedure tableof_23
as
declare
type c_list is TABLE of customer;
customer_table c_list:=c_list();
CURSOR C1 IS SELECT * FROM customers order by id;
counter int := 0;
begin
for n in C1 loop
counter := counter + 1;
customer_table(counter) := n;
end loop;
RAISE INFO '%', customer_table(3);
end;
/
call tableof_23();
INFO: (3,"(Alice,22,Bangkok,9800.98)")
tableof_23
------------
(1 row)
create or replace procedure tableof_24
as
declare
type c_list is TABLE of customers%rowtype;
customer_table c_list:=c_list();
CURSOR C1 IS SELECT * FROM customers order by id;
counter int := 0;
begin
for n in C1 loop
counter := counter + 1;
customer_table(counter) := n;
end loop;
RAISE INFO '%', customer_table(4);
end;
/
call tableof_24();
INFO: (4,"(Jim,26,Dubai,18700)")
tableof_24
------------
(1 row)
-- test row type
create type typeE as table of s_type%rowtype;
ERROR: relation does not exist when parse word.
DETAIL: relation "s_type" referenced by %ROWTYPE does not exist.
create type typeE as table of customers%rowtype;
create or replace procedure tableof_25
as
declare
customer_table typeE;
CURSOR C1 IS SELECT * FROM customers order by id;
counter int := 0;
begin
for n in C1 loop
counter := counter + 1;
customer_table(counter) := n;
end loop;
RAISE INFO '%', customer_table(4);
end;
/
call tableof_25();
INFO: (4,"(Jim,26,Dubai,18700)")
tableof_25
------------
(1 row)
-- test insert
create or replace procedure tableof_26
as
declare
type c_list is TABLE of customers%rowtype;
customer_table c_list:=c_list();
begin
customer_table(1) := (7, ('Vera' ,32, 'Paris', 22999.00));
customer_table(2) := (8, ('Vera' ,32, 'Paris', 22999.00));
insert into customers values (customer_table(1).id, customer_table(1).c_info);
insert into customers values (customer_table(2).id, customer_table(2).c_info);
end;
/
call tableof_26();
tableof_26
------------
(1 row)
select * from customers where id = 7;
id | c_info
----+-----------------------
7 | (Vera,32,Paris,22999)
(1 row)
-- expect error table[]
create or replace procedure tableof_27
as
declare
type c_list is TABLE of customers%rowtype;
customer_table c_list:=c_list();
begin
customer_table(1) := (7, ('Vera' ,32, 'Paris', 22999.00));
insert into customers values (customer_table[1].id, customer_table[1].c_info);
end;
/
ERROR: syntax error at or near "["
LINE 7: insert into customers values (customer_table[1].id, custome...
^
QUERY:
declare
type c_list is TABLE of customers%rowtype;
customer_table c_list:=c_list();
begin
customer_table(1) := (7, ('Vera' ,32, 'Paris', 22999.00));
insert into customers values (customer_table[1].id, customer_table[1].c_info);
end
-- test deault
declare
type students is table of varchar2(10);
type grades is table of integer;
marks grades := grades(98, 97, 74 + 4, (87), 92, 100); -- batch initialize --
names students default students('none'); -- default --
total integer;
begin
names := students(); -- should append NULL then do the coerce --
names := students('Vera ', 'Zera ', 'Alice', 'Jim ', 'Kevin', to_char('G') || 'auss'); -- batch insert --
total := names.count;
dbe_output.print_line('Total '|| total || ' Students');
for i in 1 .. total loop
dbe_output.print_line('Student: ' || names(i) || ' Marks: ' || marks(i));
end loop;
end;
/
Total 6 Students
Student: Vera Marks: 98
Student: Zera Marks: 97
Student: Alice Marks: 78
Student: Jim Marks: 87
Student: Kevin Marks: 92
Student: Gauss Marks: 100
create type mytype as (
id integer,
biome varchar2(100)
);
create type mytype2 as (
id integer,
locale myType
);
declare
type finaltype is table of mytype2;
aa finaltype := finaltype(
mytype2(1, mytype(1, 'ground')),
mytype2(1, mytype(2, 'air'))
);
begin
aa.extend(10);
aa(2) := (2, (3, 'water')); -- overwrite record (1, (2, 'air')) --
dbe_output.print_line('locale id is: ' || aa(1).id);
dbe_output.print_line('biome 1.3 is: ' || aa(2).locale.biome); -- ... water (not air) --
end;
/
locale id is: 1
biome 1.3 is: water
-- test of uneven brackets --
-- error out --
declare
type students is table of varchar2(10);
names students;
begin
names := students(1, 'Zera ', 'Alice', 'Jim ', 'Kevin'); -- should be able read all values correctly --
for i in 1 .. 5 loop
dbe_output.print_line('Student: ' || names(i]);
end loop;
end;
/
ERROR: mismatched brackets at or near ")"
LINE 6: dbe_output.print_line('Student: ' || names(i]);
^
QUERY: DECLARE type students is table of varchar2(10);
names students;
begin
names := students(1, 'Zera ', 'Alice', 'Jim ', 'Kevin'); -- should be able read all values correctly --
for i in 1 .. 5 loop
dbe_output.print_line('Student: ' || names(i]);
end loop;
end
-- Using composite type defined outside of precedure block --
declare
type finaltype is varray(10) of mytype2;
aa finaltype := finaltype(
mytype2(1, (1, 'ground')),
mytype2(1, (2, 'air'))
);
begin
aa(2) := (2, (3, 'water')); -- overwrite record (1, (2, 'air')) --
dbe_output.print_line('locale id is: ' || aa(1).id);
dbe_output.print_line('biome 1.3 is: ' || aa(2).locale.biome); -- ... water (not air) --
end;
/
locale id is: 1
biome 1.3 is: water
declare
type finaltype is table of mytype2;
aa finaltype := finaltype(
mytype2(1, mytype(1, 'ground')),
mytype2(1, mytype(2, 'air'))
);
begin
aa.extend(10);
aa(2) := mytype2(2, mytype(3, 'water'));
dbe_output.print_line('locale id is: ' || aa(1).id);
dbe_output.print_line('biome 1.3 is: ' || aa(2).locale.biome); -- ... water (not air) --
end;
/
locale id is: 1
biome 1.3 is: water
create type functype as (
id integer,
locale myType
);
create or replace function functype(habitat in mytype2)
return mytype2
is
ret mytype2;
begin
ret := (-1, (1, 'unknown realm'));
return ret;
end;
/
declare
type finaltype is table of mytype2;
aa finaltype := finaltype(
functype(1, mytype(1, 'ground')), -- we are prioritizing types here --
functype(1, mytype(2, 'air'))
);
begin
dbe_output.print_line('locale id is: ' || aa(1).id);
dbe_output.print_line('biome 1.2 is: ' || aa(2).locale.biome); -- air --
end;
/
locale id is: 1
biome 1.2 is: air
-- abandon type functype
drop type functype;
declare
type finaltype is table of mytype2;
aa finaltype := finaltype(
functype((1, mytype(1, 'ground'))), -- here we have to use function functype --
functype((1, mytype(2, 'air')))
);
begin
aa.extend(10);
dbe_output.print_line('locale ?? is: ' || aa(1).id);
dbe_output.print_line('biome ??? is: ' || aa(2).locale.biome); -- weird places --
end;
/
locale ?? is: -1
biome ??? is: unknown realm
drop function functype;
-- error
declare
type finaltype is table of mytype2;
aa finaltype := finaltype(
functype((1, mytype(1, 'ground'))), -- not sure --
functype((1, mytype(2, 'air')))
);
begin
aa.extend(10);
dbe_output.print_line('This message worth 300 tons of gold (once printed).');
end;
/
ERROR: function functype(record) does not exist
LINE 1: SELECT ARRAY[(functype((1, ROW(1, 'ground'))))::plpgsql_tabl...
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
QUERY: SELECT ARRAY[(functype((1, ROW(1, 'ground'))))::plpgsql_table."mytype2",
(functype((1, ROW(2, 'air'))))::plpgsql_table."mytype2"]
CONTEXT: referenced column: array
PL/pgSQL function inline_code_block line 6 during statement block local variable initialization
-- test table of array
declare
type arrayfirst is table(10) of int[];
arr arrayfirst := arrayfirst();
begin
end;
/
ERROR: syntax error at or near "("
LINE 1: DECLARE type arrayfirst is table(10) of int[];
^
QUERY: DECLARE type arrayfirst is table(10) of int[];
arr arrayfirst := arrayfirst();
begin
end
create type typeG as (a int[]);
declare
type arrayfirst is table of typeG;
arr arrayfirst := arrayfirst();
begin
arr(1) = row(ARRAY[1, 2, 3]);
dbe_output.print_line(arr(1).a[1]);
end;
/
1
-- test unreserved key word
declare
index int;
begin
index = 1;
end;
/
-- test package
create or replace package aa
is
type students is table of int;
procedure kk();
end aa;
/
create or replace package body aa
is
names students;
procedure kk
is
begin
names := students(1, 2, 3, 4, 5); -- should be able read all values correctly --
for i in 1 .. 5 loop
raise info '%', names[i];
end loop;
end;
end aa;
/
call aa.kk();
INFO: 1
INFO: 2
INFO: 3
INFO: 4
INFO: 5
kk
----
(1 row)
drop package if exists aa;
NOTICE: drop cascades to function plpgsql_table.kk()
create or replace package pck2 is
procedure p1;
type r2 is table of int index by varchar(10);
va r2;
end pck2;
/
create or replace package body pck2 is
procedure p1 as
begin
select 11 into va('a');
select 111 into va('b');
va('a') := 1111;
raise info '%,', va;
end;
end pck2;
/
call pck2.p1();
INFO: {1111,111},
p1
----
(1 row)
call pck2.p1();
INFO: {1111,111},
p1
----
(1 row)
drop package pck2;
NOTICE: drop cascades to function plpgsql_table.p1()
reset current_schema;
show current_schema;
current_schema
----------------
"$user",public
(1 row)
declare
type students is table of plpgsql_table.s_type;
a students;
begin
a(1) = (1, 'lisi', 'beijing');
end;
/
set current_schema = plpgsql_table;
-- test [:]
declare
TYPE SalTabTyp is TABLE OF integer index by varchar(10);
aa SalTabTyp;
begin
aa(1) = 1;
aa(2) = 2;
RAISE INFO '%' ,aa(1);
RAISE INFO '%' ,aa[1:2];
end;
/
INFO: 1
ERROR: index by varchar or nested table don't support two subscripts
CONTEXT: PL/pgSQL function inline_code_block line 7 at RAISE
-- test [,]
declare
TYPE SalTabTyp is TABLE OF integer index by varchar(10);
aa SalTabTyp;
begin
aa(1) = 1;
aa(2) = 2;
RAISE INFO '%' ,aa(1);
RAISE INFO '%' ,aa[1,2];
end;
/
INFO: 1
ERROR: index by varchar or nested table don't support two subscripts
CONTEXT: PL/pgSQL function inline_code_block line 7 at RAISE
-- test functions
declare
type b is table of int index by varchar;
a b;
c bool;
begin
a('a') = 1;
a('b') = 2;
c = a.exists('b');
raise info '%', c;
end;
/
INFO: t
declare
TYPE SalTabTyp is TABLE OF varchar(10) index by varchar(10);
aa SalTabTyp;
c int;
begin
aa('a') = 'abcde';
aa('b') = 'fghij';
c = aa.first;
end;
/
ERROR: invalid input syntax for integer: "a"
CONTEXT: PL/pgSQL function inline_code_block line 7 at assignment
declare
TYPE SalTabTyp is TABLE OF varchar(10) index by varchar(10);
aa SalTabTyp;
begin
aa('a') = 'abcde';
aa('b') = 'fghij';
aa.delete;
end;
/
declare
TYPE SalTabTyp is TABLE OF varchar(10) index by varchar(10);
aa SalTabTyp;
begin
aa('a') = 'abcde';
aa('b') = 'fghij';
aa.trim;
end;
/
ERROR: index by type don't support trim function at or near "aa"
LINE 6: aa.trim;
^
QUERY: DECLARE TYPE SalTabTyp is TABLE OF varchar(10) index by varchar(10);
aa SalTabTyp;
begin
aa('a') = 'abcde';
aa('b') = 'fghij';
aa.trim;
end
declare
TYPE SalTabTyp is TABLE OF varchar(10) index by int;
aa SalTabTyp;
begin
aa(1) = 'abcde';
aa(2) = 'fghij';
aa.trim;
end;
/
ERROR: index by type don't support trim function at or near "aa"
LINE 6: aa.trim;
^
QUERY: DECLARE TYPE SalTabTyp is TABLE OF varchar(10) index by int;
aa SalTabTyp;
begin
aa(1) = 'abcde';
aa(2) = 'fghij';
aa.trim;
end
declare
TYPE SalTabTyp is TABLE OF varchar(10) index by varchar(10);
aa SalTabTyp;
begin
aa.extend;
aa('a') = 'abcde';
aa('b') = 'fghij';
end;
/
ERROR: index by type don't support extend function at or near "aa"
LINE 4: aa.extend;
^
QUERY: DECLARE TYPE SalTabTyp is TABLE OF varchar(10) index by varchar(10);
aa SalTabTyp;
begin
aa.extend;
aa('a') = 'abcde';
aa('b') = 'fghij';
end
declare
TYPE SalTabTyp is TABLE OF varchar(10) index by int;
aa SalTabTyp;
begin
aa.extend;
aa(1) = 'abcde';
aa(2) = 'fghij';
end;
/
ERROR: index by type don't support extend function at or near "aa"
LINE 4: aa.extend;
^
QUERY: DECLARE TYPE SalTabTyp is TABLE OF varchar(10) index by int;
aa SalTabTyp;
begin
aa.extend;
aa(1) = 'abcde';
aa(2) = 'fghij';
end
declare
TYPE SalTabTyp is TABLE OF varchar(10) index by varchar(10);
aa SalTabTyp;
c varchar(10);
begin
aa('a') = 'abcde';
aa('b') = 'fghij';
c = aa.next(aa.first);
end;
/
declare
TYPE SalTabTyp is TABLE OF varchar(10) index by varchar(10);
aa SalTabTyp;
c varchar(10);
begin
aa('a') = 'abcde';
aa('b') = 'fghij';
c = aa.prior('a');
end;
/
declare
TYPE SalTabTyp is TABLE OF varchar(10) index by varchar(10);
aa SalTabTyp;
c varchar(10);
begin
aa('a') = 'abcde';
aa('b') = 'fghij';
c = aa.last;
end;
/
declare
TYPE SalTabTyp is TABLE OF varchar(10) index by varchar(10);
aa SalTabTyp;
c int;
begin
aa('a') = 'abcde';
RAISE INFO '%', aa.exists('a');
end;
/
INFO: t
declare
TYPE SalTabTyp is TABLE OF varchar(10) index by integer;
aa SalTabTyp;
c int;
begin
aa(1) = 'a';
aa(-1) = 'c';
aa(2) = 'b';
raise info '%', aa.next(1);
raise info '%', aa.prior(1);
end;
/
INFO: 2
INFO: -1
declare
type ta is table of varchar(100);
tb constant ta := ta('10','11');
begin
tb(1) := 12;
dbe_output.print_line(tb[1]);
end;
/
ERROR: "tb" is declared CONSTANT
LINE 4: tb(1) := 12;
^
QUERY: DECLARE type ta is table of varchar(100);
tb constant ta := ta('10','11');
begin
tb(1) := 12;
dbe_output.print_line(tb[1]);
end
declare
type ta is table of varchar(100);
tb constant ta := ta('10','11');
begin
tb := ta('12','13');
dbe_output.print_line(tb[1]);
end;
/
ERROR: "tb" is declared CONSTANT
LINE 4: tb := ta('12','13');
^
QUERY: DECLARE type ta is table of varchar(100);
tb constant ta := ta('10','11');
begin
tb := ta('12','13');
dbe_output.print_line(tb[1]);
end
reset sql_beta_feature ;
create or replace package pcknesttype is
type aa is table of int;
type bb is table of aa;
procedure proc1();
end pcknesttype;
/
create or replace package body pcknesttype
is
mytab aa;
my2 bb;
procedure proc1
is begin
mytab := aa(1,2,3,4);
my2 := bb(mytab);
end;
end pcknesttype;
/
create or replace procedure tableof_nest1()
is
type data_type1 is table of s_type index by integer;
type data_table_type1 is table of data_type1 index by integer;
MyTab data_type1;
tmp_y data_type1;
yy data_table_type1;
begin
MyTab(1).id := 1;
MyTab(2).name := 'B';
MyTab(3).addr := 'addr';
yy(0) := MyTab;
yy(1)(1).id := 1;
yy(1)(1).name := 'yy';
RAISE INFO 'call yy: %' ,yy(1)(1);
RAISE INFO 'call yy count: %' ,yy(0).count;
tmp_y := yy(1);
--RAISE INFO 'call yy(1) next: %' ,tmp_y.next(1);
--RAISE INFO 'call yy first: %' ,yy.first;
--RAISE INFO 'call yy next: %' ,yy.next(1);
end;
/
call tableof_nest1();
INFO: call yy: (1,yy,)
INFO: call yy count: 1
tableof_nest1
---------------
(1 row)
create or replace procedure tableof_nest2()
is
type data_type1 is table of varchar2(100) index by integer;
type data_table_type1 is table of data_type1 index by integer;
MyTab data_type1;
tmp_y data_type1;
yy data_table_type1;
begin
MyTab(1) := 'A';
MyTab(2) := 'B';
MyTab(3) := 'C';
yy(0) := MyTab;
yy(1)(1) := 'o';
RAISE INFO 'call yy: %' ,yy(1)(1);
RAISE INFO 'call yy count: %' ,yy(0).count;
tmp_y := yy(1);
--RAISE INFO 'call yy(1) next: %' ,tmp_y.next(1);
--RAISE INFO 'call yy first: %' ,yy.first;
--RAISE INFO 'call yy next: %' ,yy.next(1);
end;
/
call tableof_nest2();
INFO: call yy: o
INFO: call yy count: 1
tableof_nest2
---------------
(1 row)
create or replace procedure tableof_nest3()
is
type data_type1 is table of varchar2(100) index by varchar2(24);
type data_table_type1 is table of data_type1 index by varchar2(24);
MyTab data_type1;
tmp_y data_type1;
yy data_table_type1;
begin
MyTab('a') := 'A';
MyTab('b') := 'B';
MyTab('c') := 'C';
yy('a') := MyTab;
yy('b')('c') := 'o';
RAISE INFO 'call yy: %' ,yy('a')('c');
RAISE INFO 'call yy count: %' ,yy('a').count;
tmp_y := yy('b');
--RAISE INFO 'call yy next: %' ,tmp_y.next('c');
--RAISE INFO 'call yy first: %' ,tmp_y.first;
--RAISE INFO 'call yy next: %' ,yy.next('a');
end;
/
call tableof_nest3();
INFO: call yy: C
INFO: call yy count: 1
tableof_nest3
---------------
(1 row)
DECLARE
TYPE r1 is TABLE OF int;
type r2 is table of r1;
emp_id r2;
BEGIN
emp_id(1)(1) := 5*7784;
raise info '%,%', emp_id,emp_id(1)(1);
END;
/
ERROR: Don't print entire nest table of value in raise statement
CONTEXT: PL/pgSQL function inline_code_block line 6 at RAISE
create type type001 as(c1 int,c2 varchar);
create type type002 as(c1 type001,c2 type001.c2%type,c4 int);
NOTICE: type reference type001.c2%TYPE converted to character varying
create type type003 as table of type002;
create type type004 as(c1 type003,c2 int);
create or replace procedure proc_1 as
typecol type004;
begin
typecol.c1(1).c1.c1=1;
typecol.c2=1;
raise info 'typecol %',typecol.c1(1).c1.c1;
raise info 'typecol %',typecol.c2;
raise info 'typecol %',typecol;
end;
/
call proc_1();
INFO: typecol 1
INFO: typecol 1
INFO: typecol ("{""(\\""(1,)\\"",,)""}",1)
proc_1
--------
(1 row)
drop type type_nest_23,type_nest_22,type_nest_24,type_nest_25 cascade;
ERROR: type "type_nest_23" does not exist
drop table type_nest_21 cascade;
ERROR: table "type_nest_21" does not exist
create table type_nest_21 (c1 int,c2 text, c3 date);
create type type_nest_22 as(c1 type_nest_21,c2 type_nest_21.c2%type,c3 type_nest_21%rowtype);
NOTICE: type reference type_nest_21.c2%TYPE converted to text
create type type_nest_23 is table of type_nest_22;
create type type_nest_24 is table of type_nest_21;
create type type_nest_25 as(c1 type_nest_21,c2 type_nest_23);
declare
type type1 is varray(6) of varchar2(10);
TYPE type2 is TABLE OF type_nest_21;
TYPE type3 is TABLE OF type2;
TYPE type4 is TABLE OF type3;
vtype5 type3;
vtype6 type_nest_25;
begin
vtype5(1)(1).c2 := 'abc';
raise info '%', vtype5(1)(1).c2;
end;
/
INFO: abc
declare
type type1 is varray(6) of varchar2(10);
TYPE type2 is TABLE OF type_nest_21;
TYPE type3 is TABLE OF type2;
vtype6 type3;
begin
vtype6(1)(1)(1).c2 := 'abc';
end;
/
ERROR: subscripts list has members more than tableof value vtype6 expected
CONTEXT: PL/pgSQL function inline_code_block line 6 at assignment
declare
TYPE record_user1 is table of type_nest_21;
TYPE record_user2 is table of record_user1;
TYPE record_user3 is table of record_user2;
v_record_user2 record_user2;
v_record_user3 record_user3;
begin
v_record_user2(1) :=1;
end;
/
ERROR: array value must start with "{" or dimension information
CONTEXT: PL/pgSQL function inline_code_block line 7 at assignment
declare
TYPE record_user1 is table of type_nest_21;
TYPE record_user2 is table of record_user1;
TYPE record_user3 is table of record_user2;
v_record_user2 record_user2;
v_record_user3 record_user3;
begin
v_record_user3(1)(1):=1;
v_record_user2(1).c1 :=1;
end;
/
ERROR: malformed record literal: "1"
DETAIL: Missing left parenthesis.
CONTEXT: PL/pgSQL function inline_code_block line 7 at assignment
drop table customers cascade;
NOTICE: drop cascades to type _customers[]
create table customers (
id number(10) not null,
c_name varchar2(50),
c_age number(8) not null,
c_address varchar2(50),
salary float(2) not null,
constraint customers_pk primary key (id)
);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "customers_pk" for table "customers"
--test collect in null
declare
cursor c_customers is select c_name from customers order by id;
type c_list is table of customers.c_name%type index by integer;
name_arr c_list := c_list();
begin
name_arr(2) = (-1, 'Vera' ,32, 'Paris', 22999.00);
name_arr(7) = (-1, 'Vera' ,32, 'Paris', 22999.00);
-- bulk collect + cursor
open c_customers;
fetch c_customers bulk collect into name_arr;
close c_customers;
raise info '%', name_arr.count;
raise info '%', name_arr.last;
raise info '%', name_arr.exists(7);
end;
/
INFO: 0
INFO: <NULL>
INFO: f
declare
type id_list is varray(6) of customers.id%type;
id_arr id_list;
begin
id_arr(1) = 1;
raise info '%', id_arr;
select id bulk collect into id_arr from customers order by id DESC;
raise info '%', id_arr;
end;
/
INFO: {1}
INFO: {}
create type mytype1 as (
id integer,
biome varchar2(100)
);
-- success, multi target support
declare
type tab is varray(6) of mytype1;
tab1 tab := tab();
begin
tab1(1) = (1,'a');
raise info '%', tab1;
select id, c_name bulk collect into tab1 from customers order by id DESC;
raise info '%', tab1;
end;
/
INFO: {"(1,a)"}
INFO: {}
insert into customers (id, c_name, c_age, c_address, salary) values (1, 'Vera' ,32, 'Paris', 22999.00);
--test bulk collect into
declare
cursor c_customers is select c_name from customers order by id;
type c_list is table of customers.c_name%type index by integer;
name_arr c_list := c_list();
begin
name_arr(2) = (-1, 'Vera' ,32, 'Paris', 22999.00);
name_arr(7) = (-1, 'Vera' ,32, 'Paris', 22999.00);
-- bulk collect + cursor
open c_customers;
fetch c_customers bulk collect into name_arr;
exit when c_customers%NOTFOUND;
close c_customers;
raise info '%', name_arr.count;
raise info '%', name_arr.last;
raise info '%', name_arr.exists(7);
end;
/
INFO: 1
INFO: 1
INFO: f
insert into customers (id, c_name, c_age, c_address, salary) values (2, '' ,25, 'London', 5999.00); -- a missing value here
insert into customers (id, c_name, c_age, c_address, salary) values (3, 'Alice' ,22, 'Bangkok', 9800.98);
insert into customers (id, c_name, c_age, c_address, salary) values (4, 'Jim' ,26, 'Dubai', 18700.00);
insert into customers (id, c_name, c_age, c_address, salary) values (5, 'Kevin' ,28, 'Singapore', 18999.00);
insert into customers (id, c_name, c_age, c_address, salary) values (6, 'Gauss' ,42, 'Beijing', 32999.00);
--test bulk collect into
declare
cursor c_customers is select c_name from customers order by id;
type c_list is table of customers.c_name%type index by integer;
name_arr c_list := c_list();
begin
-- bulk collect + cursor
open c_customers;
fetch c_customers bulk collect into name_arr limit 4;
exit when c_customers%NOTFOUND;
close c_customers;
for i in 1..6 loop
dbe_output.print_line('name(' || i || '): ' || name_arr(i));
end loop;
-- assign values directly
name_arr := ARRAY['qqqq', 'sfsafds', 'sadsadas'];
for i in 1..6 loop
dbe_output.print_line('name(' || i || '): ' || name_arr(i));
end loop;
end;
/
name(1): Vera
name(2):
name(3): Alice
name(4): Jim
name(5):
name(6):
name(1): qqqq
name(2): sfsafds
name(3): sadsadas
name(4):
name(5):
name(6):
declare
cursor c_customers is select c_name from customers order by id;
type c_list is table of customers.c_name%type index by varchar;
name_arr c_list := c_list();
begin
-- bulk collect + cursor
open c_customers;
fetch c_customers bulk collect into name_arr limit 4;
exit when c_customers%NOTFOUND;
close c_customers;
end;
/
ERROR: index by varchar type name_arr don't support bulk collect into. at or near ";"
LINE 7: fetch c_customers bulk collect into name_arr limit 4;
^
QUERY: DECLARE cursor c_customers is select c_name from customers order by id;
type c_list is table of customers.c_name%type index by varchar;
name_arr c_list := c_list();
begin
-- bulk collect + cursor
open c_customers;
fetch c_customers bulk collect into name_arr limit 4;
exit when c_customers%NOTFOUND;
close c_customers;
end
-- table of null && delete
declare
cursor c_customers is select c_name from customers order by id;
type c_list is table of customers.c_name%type index by integer;
name_arr c_list := c_list();
begin
-- bulk collect + cursor
open c_customers;
fetch c_customers bulk collect into name_arr;
name_arr.delete();
close c_customers;
raise info '%', name_arr.count;
raise info '%', name_arr.last;
raise info '%', name_arr.exists(7);
end;
/
INFO: 0
INFO: <NULL>
INFO: f
create table pro_tblof_tbl_018_1(c1 int,c2 varchar(20));
create table pro_tblof_tbl_018(c1 int,c2 pro_tblof_tbl_018_1);
create type pro_tblof_018 is table of pro_tblof_tbl_018%rowtype;
insert into pro_tblof_tbl_018 values (1,(2,'aaa'));
create or replace procedure pro_tblof_pro_018_11()
as
tblof001 pro_tblof_018;
cursor cor1 is select c2 from pro_tblof_tbl_018 order by c1 desc;
cursor cor2 is select c1 from pro_tblof_tbl_018 order by c1 desc;
tblcount int;
begin
select count(*) into tblcount from pro_tblof_tbl_018;
for i in 1..tblcount
loop
--open cor1;
-- fetch cor1 bulk collect into tblof001(i).c2;
-- EXIT WHEN cor1%NOTFOUND;
--close cor1;
open cor2;
fetch cor2 bulk collect into tblof001(i).c1;
exit when cor2%notfound;
close cor2;
i=i+1;
end loop;
for i in tblof001.first..tblof001.last
loop
if tblof001(i) is null then
tblof001(i)=tblof001(tblof001.next(i));
end if;
dbe_output.print_line('tblof001 ('||i||')is '||tblof001(i).c1||'-----'||tblof001(i).c2);
end loop;
raise info 'tblof001 is %',tblof001;
end;
/
call pro_tblof_pro_018_11();
ERROR: Unsupported bulk collect into target
DETAIL: Unable to recognize the given bulk collect into target
CONTEXT: PL/pgSQL function pro_tblof_pro_018_11() line 14 at FETCH
create or replace procedure p155() as
type t is table of varchar2 index by integer;
v t;
begin
raise info '%', v.count;
for i in 1..v.count loop
v(i):=0;
end loop;
end;
/
call p155();
INFO: 0
p155
------
(1 row)
create or replace procedure p156() as
type t is table of varchar2 index by varchar;
v t;
begin
raise info '%', v.count;
for i in 1..v.count loop
v(i):=0;
end loop;
end;
/
call p156();
INFO: 0
p156
------
(1 row)
create or replace procedure table_column
is
type rec_type is record (name varchar2(100), epno int);
TYPE SalTabTyp as TABLE of rec_type index by BINARY_INTEGER;
aa SalTabTyp;
begin
aa(0).epno = 1;
raise info '%', aa;
select '' into aa(0).name;
raise info '%', aa;
end;
/
call table_column();
INFO: {"(,1)"}
INFO: {"(,1)"}
table_column
--------------
(1 row)
create table pkgtbl054(c0 int,c1 number(5),c2 varchar2(20),c3 clob,c4 blob);
insert into pkgtbl054 values(1,1,'varchar1',repeat('clob1',20),'abcdef1');
insert into pkgtbl054 values(2,2,'varchar10',repeat('clob2',20),'abcdef2');
create type type0011 as(c0 int,c1 number(5),c2 varchar2(20),c3 clob,c4 blob);
create or replace package pkg054
is
type type0011 is table of type0011%rowtype index by varchar2(20);
type type002 is table of type0011.c2%type index by integer;
col1 type0011;
col2 type002;
procedure proc054_1(col3 type0011,col4 type002);
function proc054_2(col5 int) return integer;
end pkg054;
/
create or replace package body pkg054
is
procedure proc054_1(col3 type0011,col4 type002)
is
begin
raise info 'col13 is %',col3;
raise info 'col14 is %',col4;
exception
when others then
raise info 'sqlerrm is %',sqlerrm;
end;
function proc054_2(col5 int) return integer
as
begin
col1('1').c0:=128909887;
col1('1').c1:=12345;
col1('2').c2:='var2';
col1('2').c3:='clobcol1';
col1('2').c4:='123456';
col2(1):=col1('2').c2;
col2(3):=col1('1').c3;
raise info 'col1 is %',col1;
raise info 'col2 is %',col2;
proc054_1(col3=>pkg054.col1,col4=>pkg054.col2);
return 1;
end;
end pkg054;
/
call pkg054.proc054_2(1);
INFO: col1 is {"(128909887,12345,,,)","(,,var2,clobcol1,123456)"}
INFO: col2 is {var2,NULL}
INFO: col13 is {"(128909887,12345,,,)","(,,var2,clobcol1,123456)"}
CONTEXT: SQL statement "CALL proc054_1(col3=>pkg054.col1,col4=>pkg054.col2)"
PL/pgSQL function proc054_2(integer) line 12 at PERFORM
INFO: col14 is {var2,NULL}
CONTEXT: SQL statement "CALL proc054_1(col3=>pkg054.col1,col4=>pkg054.col2)"
PL/pgSQL function proc054_2(integer) line 12 at PERFORM
proc054_2
-----------
1
(1 row)
create or replace package body pkg054
is
procedure proc054_1(col3 type0011,col4 type002)
is
begin
raise info 'col13 is %',col3;
raise info 'col14 is %',col4;
exception
when others then
raise info 'sqlerrm is %',sqlerrm;
end;
function proc054_2(col5 int) return integer
as
begin
col1('1').c0:=128909887;
col1('1').c1:=12345;
col1('2').c2:='var2';
col1('2').c3:='clobcol1';
col1('2').c4:='123456';
col2(1):=col1('2').c2;
col2(3):=col1('1').c3;
raise info 'col1 is %',col1;
raise info 'col2 is %',col2;
proc054_1(pkg054.col1,pkg054.col2);
return 1;
end;
end pkg054;
/
call pkg054.proc054_2(1);
INFO: col1 is {"(128909887,12345,,,)","(,,var2,clobcol1,123456)"}
INFO: col2 is {var2,NULL}
INFO: col13 is {"(128909887,12345,,,)","(,,var2,clobcol1,123456)"}
CONTEXT: SQL statement "CALL proc054_1(pkg054.col1,pkg054.col2)"
PL/pgSQL function proc054_2(integer) line 12 at PERFORM
INFO: col14 is {var2,NULL}
CONTEXT: SQL statement "CALL proc054_1(pkg054.col1,pkg054.col2)"
PL/pgSQL function proc054_2(integer) line 12 at PERFORM
proc054_2
-----------
1
(1 row)
drop package pkg054;
NOTICE: drop cascades to 2 other objects
DETAIL: drop cascades to function plpgsql_table.proc054_1(_type0011[],_varchar[])
drop cascades to function plpgsql_table.proc054_2(integer)
drop type type_nest_23,type_nest_22,type_nest_24,type_nest_25 cascade;
NOTICE: drop cascades to composite type type_nest_25 column c2
drop table type_nest_21;
create or replace package pkg049
is
type type001 is table of number(8) index by varchar2(30);
type type002 is record(c1 type001,c2 varchar2(30));
function proc049_2(col1 int) return type001;
end pkg049;
/
ERROR: table of index type is not supported as function return type.
DETAIL: N/A
create or replace function tableof_return_1(col1 int) return s_type[]
is
type type001 is table of s_type index by varchar2(30);
a type001;
begin
return a;
end;
/
ERROR: table of index type is not supported as function return type. at or near ";"
LINE 5: return a;
^
QUERY: DECLARE
type type001 is table of s_type index by varchar2(30);
a type001;
begin
return a;
end
create or replace function tableof_return_2(col1 int) return integer
is
type type001 is table of s_type index by varchar2(30);
a type001;
begin
a(1) = (1, 'lisi', 'beijing');
return a(1).id;
end;
/
call tableof_return_2(1);
tableof_return_2
------------------
1
(1 row)
create or replace function tableof_return_3(col1 int) return integer
is
type type001 is table of integer index by varchar2(30);
a type001;
begin
a(1) = 1;
a(2) = 2;
return a(1);
end;
/
call tableof_return_3(1);
tableof_return_3
------------------
1
(1 row)
-- test varchar as index and text as index
drop table t1;
ERROR: table "t1" does not exist
create table t1 (a varchar2(100), b varchar2(100), c number(10,0), d number(10,0), e number(10,0));
create or replace package pck1 as
type ra is table of varchar2 index by varchar2(100);
procedure p1 (v01 out ra);
end pck1;
/
create or replace package body pck1 as
type rb is table of t1 index by varchar;
v02 rb;
v_buff varchar2(1000);
procedure p1(v01 out ra) as
v_value varchar2(200);
v_index varchar2(200);
i int := 1;
begin
v_value := 'testdaa11'||i;
v_index := 'test_' || i;
v02(v_index).a = v_value;
v01(v02(v_index).a) := v_value ||i;
raise info 'v02.a : %', v02(v_index).a;
raise info 'v01.first : %', v01.first();
raise info 'v01(testdaa111) : %', v01('testdaa111');
raise info 'v01(v01.first()) : %' ,v01(v01.first());
raise info 'v01(v02(v_index).a) : %' ,v01(v02(v_index).a);
end;
end pck1;
/
call pck1.p1('');
INFO: v02.a : testdaa111
INFO: v01.first : testdaa111
INFO: v01(testdaa111) : testdaa1111
INFO: v01(v01.first()) : testdaa1111
INFO: v01(v02(v_index).a) : testdaa1111
v01
---------------
{testdaa1111}
(1 row)
drop package pck1;
NOTICE: drop cascades to function plpgsql_table.p1()
drop table t1;
create table blob1(c1 blob);
create or replace procedure testblob1(count int)
IS
begin
execute immediate 'insert into blob1 values(:p1);' using 1::bit(100)::varchar::blob;
end;
/
call testblob1(1);
testblob1
-----------
(1 row)
drop table blob1;
-- test table of as out param
create or replace package pck1 as
type r1 is table of int index by int;
type r2 is record(a int, b int);
procedure p1;
procedure p2(va out r2,vb out r1);
procedure p2(vc int, va out r2,vb out r1);
end pck1;
/
create or replace package body pck1 as
procedure p1 as
va r2;
vb r1;
begin
p2(va, vb);
raise info '%',vb.first;
end;
procedure p2(va out r2, vb out r1) as
vc int;
begin
p2(vc,va,vb);
end;
procedure p2(vc int, va out r2, vb out r1) as
begin
va := (1,2);
vb(2) := 2;
end;
end pck1;
/
call pck1.p1();
INFO: 2
p1
----
(1 row)
drop package pck1;
NOTICE: drop cascades to 3 other objects
DETAIL: drop cascades to function plpgsql_table.p1()
drop cascades to function plpgsql_table.p2()
drop cascades to function plpgsql_table.p2(integer)
create or replace package pkgnest002
as
type ty0 is table of integer index by integer;
type ty1 is table of ty0;
type ty3 is table of ty1 ;
procedure p1;
end pkgnest002;
/
create or replace package body pkgnest002
as
procedure p1
is
v1 ty0:=ty0();
v2 ty1:=ty1();
v21 ty1;
v22 ty3;
v31 ty3:=ty3();
begin
raise info 'v1 is %',v1;
raise info 'v31 is %',v31;
v1(1):=1;
v1(2):=2;
v2(1):=v1;
v1(5):=5;
v2(2):=v1;
raise info 'v1 is %',v1(1);
raise info 'v2 is %',v2(1);
raise info 'v2 is %',v2(2);
v31(4):=v21;
raise info 'v31(4) is %', v31(4);
v21(1)(1):=-1;
raise info 'v21(1) is %', v21(1);
v21(2)(2):=-2;
v31(3):=v21;
v22:=v31;
raise info 'v31 is %', v31(3)(2);
v22:=v2;
end;
end pkgnest002;
/
call pkgnest002.p1();
INFO: v1 is {}
INFO: v31 is {}
INFO: v1 is 1
INFO: v2 is {1,2}
INFO: v2 is {1,2,5}
INFO: v31(4) is <NULL>
INFO: v21(1) is {-1}
INFO: v31 is {-2}
ERROR: Nest tableof var v22 assigned is mismatch excepted nest layers.
CONTEXT: PL/pgSQL function p1() line 26 at assignment
call pkgnest002.p1();
INFO: v1 is {}
INFO: v31 is {}
INFO: v1 is 1
INFO: v2 is {1,2}
INFO: v2 is {1,2,5}
INFO: v31(4) is <NULL>
INFO: v21(1) is {-1}
INFO: v31 is {-2}
ERROR: Nest tableof var v22 assigned is mismatch excepted nest layers.
CONTEXT: PL/pgSQL function p1() line 26 at assignment
create or replace package pkgnest_auto
as
type ty1 is table of varchar2(20) index by varchar2;
type ty2 is table of ty1 index by varchar2;
type ty3 is table of ty2 index by varchar2;
function p1() return varchar2(20);
pv1 ty1;
pv2 ty2;
pv3 ty3;
end pkgnest_auto;
/
create or replace package body pkgnest_auto
as
function p1() return varchar2(20)
is
PRAGMA AUTONOMOUS_TRANSACTION;
begin
pv1(1):=10000;
pv1(2):=20000;
pv1(3):=30000;
pv2(1):=pv1;
pv2(2):=pv1;
pv2(3):=pv1;
pv3(1):=pv2;
pv3(2):=pv2;
pv3(3):=pv2;
return pv6.first;
end;
end pkgnest_auto;
/
ERROR: Un-support feature: nest tableof variable "pv2" not support pass through autonm function
CONTEXT: compilation of PL/pgSQL package near line 6
call pkgnest_auto.p1();
ERROR: package body not defined
-- nested table of in auto session
create or replace package pck1 as
type r1 is table of int;
type r2 is table of r1;
va r2;
procedure p1;
end pck1;
/
create or replace procedure p2() as
va int;
begin
va := pck1.va(1)(1);
end;
/
create or replace package body pck1 as
procedure p1 as
PRAGMA AUTONOMOUS_TRANSACTION;
begin
p2();
end;
end pck1;
/
call pck1.p1();
ERROR: ERROR: Un-support feature: nest tableof variable "va" not support pass through autonm function
CONTEXT: referenced column: va
SQL statement "SELECT pck1.va[1][1]"
PL/pgSQL function p2() line 4 at assignment
SQL statement "CALL p2()"
PL/pgSQL function p1() line 4 at PERFORM
referenced column: p1
create or replace procedure p2() as
va int;
begin
pck1.va(1)(1) := 1;
end;
/
call pck1.p1();
ERROR: ERROR: Un-support feature: nest tableof variable "va" not support pass through autonm function
CONTEXT: PL/pgSQL function p2() line 4 at assignment
SQL statement "CALL p2()"
PL/pgSQL function p1() line 4 at PERFORM
referenced column: p1
drop procedure p2();
drop package pck1;
NOTICE: drop cascades to function plpgsql_table.p1()
create or replace package pkgnest021
as
type ty1 is table of varchar2(20);
type ty2 is table of ty1;
type ty3 is table of ty2;
type ty4 is table of ty3;
type ty5 is table of ty4;
type ty6 is table of ty5;
procedure p1;
pv1 ty1;
pv2 ty2;
pv3 ty3;
pv4 ty4;
pv5 ty5;
pv6 ty6;
end pkgnest021;
/
create or replace package body pkgnest021
as
procedure p1
is
v1 ty6;
begin
pv6.extend();
pv6(1)(1)(1)(1)(1)(1):='1'; pv6(1)(1)(1)(1)(1)(2):='2'; pv6(1)(1)(1)(1)(1)(4):='3'; pv6(2)(1)(1)(1)(3)(1):='4'; pv6(2)(1)(1)(1)(3)(2):='5'; pv6(2)(1)(1)(1)(3)(3):='6'; pv6(4)(1)(1)(4)(3)(1):='7'; pv6(4)(1)(1)(5)(3)(2):='8'; pv6(4)(1)(1)(6)(3)(3):='9';
end;
end pkgnest021;
/
drop package pkgnest021;
NOTICE: drop cascades to function plpgsql_table.p1()
---- clean ----
drop type s_type;
ERROR: cannot drop type s_type because other objects depend on it
DETAIL: type _s_type[] depends on type s_type[]
function plpgsql_table.tableof_1(_s_type[]) depends on type _s_type[]
function tableof_4() depends on type s_type[]
function tableof_5() depends on type s_type[]
function tableof_9() depends on type s_type[]
HINT: Use DROP ... CASCADE to drop the dependent objects too.
drop type typeA;
ERROR: cannot drop type _s_type[] because other objects depend on it
DETAIL: function plpgsql_table.tableof_1(_s_type[]) depends on type _s_type[]
HINT: Use DROP ... CASCADE to drop the dependent objects too.
drop type typeB;
drop type s_type cascade;
NOTICE: drop cascades to 5 other objects
DETAIL: drop cascades to type _s_type[]
drop cascades to function plpgsql_table.tableof_1(_s_type[])
drop cascades to function tableof_4()
drop cascades to function tableof_5()
drop cascades to function tableof_9()
drop type typeC;
drop type typeE;
ERROR: type "typee" does not exist
drop type typeG;
drop type s_type_extend cascade;
NOTICE: drop cascades to 2 other objects
DETAIL: drop cascades to type _s_type_extend[]
drop cascades to function tableof_8()
drop type typeA_ext;
ERROR: type "typea_ext" does not exist
drop type info cascade;
NOTICE: drop cascades to 5 other objects
DETAIL: drop cascades to composite type customer column c_info
--?.*
--?.*
--?.*
--?.*
drop type customer;
drop type mytype cascade;
NOTICE: drop cascades to composite type mytype2 column locale
drop type mytype2;
drop procedure tableof_1;
ERROR: function tableof_1 does not exist
drop procedure tableof_2;
drop procedure tableof_3;
drop function tableof_6;
drop function tableof_7;
drop function tableof_8;
ERROR: function tableof_8 does not exist
drop procedure tableof_10;
drop procedure tableof_11;
drop procedure tableof_12;
drop procedure tableof_13;
drop procedure tableof_14;
drop procedure tableof_16;
drop procedure tableof_17;
drop procedure tableof_18;
drop procedure tableof_19;
drop procedure tableof_21;
drop procedure tableof_22;
drop procedure tableof_23;
drop procedure tableof_24;
drop procedure tableof_25;
drop procedure tableof_26;
drop procedure tableof_27;
ERROR: function tableof_27 does not exist
drop package pcknesttype;
NOTICE: drop cascades to function plpgsql_table.proc1()
drop procedure tableof_nest2;
drop procedure tableof_nest3;
drop table customers;
drop package pkgnest002;
NOTICE: drop cascades to function plpgsql_table.p1()
drop package pkgnest_auto;
NOTICE: drop cascades to function plpgsql_table.p1()
drop schema if exists plpgsql_table cascade;
NOTICE: drop cascades to 24 other objects
DETAIL: drop cascades to type _varchar[]
drop cascades to type _varchar[]
drop cascades to function tableof_alter()
drop cascades to type s_type_1
drop cascades to function tableof_20()
drop cascades to function tableof_nest1()
drop cascades to type type001
drop cascades to type type002
drop cascades to type _type002[]
drop cascades to type type004
drop cascades to function proc_1()
drop cascades to type mytype1
drop cascades to table pro_tblof_tbl_018_1
drop cascades to table pro_tblof_tbl_018
drop cascades to type _pro_tblof_tbl_018[]
drop cascades to function pro_tblof_pro_018_11()
drop cascades to function p155()
drop cascades to function p156()
drop cascades to function table_column()
drop cascades to table pkgtbl054
drop cascades to type type0011
drop cascades to function tableof_return_2(integer)
drop cascades to function tableof_return_3(integer)
drop cascades to function testblob1(integer)