test.sql

text/x-sql

Download

begin;

create type composite AS (
	foo	integer,
	bar	varchar(256)
);

create or replace function foo() returns void aS $$
	declare
		-- base types
		a	boolean;
		b	integer;
		c	numeric(8,2);
		d	char;
		e	varchar(12);
		f	text;
		g	composite;

		-- arrays
		aa	boolean[];
		ba	integer[];
		ca	numeric(8,2)[];
		da	char[];
		ea	varchar(12)[];
		fa	text[];
		ga	composite[];
		
		-- copy scalar types
		type_a	a%TYPE[5];
		type_b	b%TYPE[3][3];
		type_c	c%TYPE[];
		type_d	d%TYPE[][][];
		type_e	e%TYPE[][7];
		type_f	f%TYPE[];
		type_g1	composite.foo%TYPE[12][5][][][];
		type_g2	composite.bar%TYPE[][][];

		-- copy array types
		typearr_a	aa%TYPE[5];
		typearr_b	ba%TYPE[3][3];
		typearr_c	ca%TYPE[];
		typearr_d	da%TYPE[][1][];
		typearr_e	ea%TYPE[][7];
		typearr_f	fa%TYPE[][];
		typearr_g	ga%TYPE[];
	begin
		--- shot types
		raise notice '%', pg_typeof(type_a);
		raise notice '%', pg_typeof(type_b);
		raise notice '%', pg_typeof(type_c);
		raise notice '%', pg_typeof(type_d);
		raise notice '%', pg_typeof(type_e);
		raise notice '%', pg_typeof(type_f);
		raise notice '%', pg_typeof(type_g1);
		raise notice '%', pg_typeof(type_g2);

		--- 
		raise notice '%', pg_typeof(typearr_a);
		raise notice '%', pg_typeof(typearr_b);
		raise notice '%', pg_typeof(typearr_c);
		raise notice '%', pg_typeof(typearr_d);
		raise notice '%', pg_typeof(typearr_e);
		raise notice '%', pg_typeof(typearr_f);
		raise notice '%', pg_typeof(typearr_g);
	end;
$$ language plpgsql;

select foo();

rollback;