plpgsql.sql

PostgreSQL 8.1.4的源码 适用于Linux下的开源数据库系统

-- Buy some phones, plug them into the wall and patch the
-- phone lines to the corresponding patchfield slots.
--
insert into PHone values ('PH.hc001', 'Hicom standard', 'WS.001.1a');
update PSlot set slotlink = 'PS.base.ta1' where slotname = 'PS.base.a1';
insert into PHone values ('PH.hc002', 'Hicom standard', 'WS.002.1a');
update PSlot set slotlink = 'PS.base.ta5' where slotname = 'PS.base.b1';
insert into PHone values ('PH.hc003', 'Hicom standard', 'WS.002.2a');
update PSlot set slotlink = 'PS.base.tb2' where slotname = 'PS.base.b3';
insert into PHone values ('PH.fax001', 'Canon fax', 'WS.001.2a');
update PSlot set slotlink = 'PS.base.ta2' where slotname = 'PS.base.a3';

--
-- Install a hub at one of the patchfields, plug a computers
-- ethernet interface into the wall and patch it to the hub.
--
insert into Hub values ('base.hub1', 'Patchfield PF0_1 hub', 16);
insert into System values ('orion', 'PC');
insert into IFace values ('IF', 'orion', 'eth0', 'WS.002.1b');
update PSlot set slotlink = 'HS.base.hub1.1' where slotname = 'PS.base.b2';

--
-- Now we take a look at the patchfield
--
select * from PField_v1 where pfname = 'PF0_1' order by slotname;
select * from PField_v1 where pfname = 'PF0_2' order by slotname;

--
-- Finally we want errors
--
insert into PField values ('PF1_1', 'should fail due to unique index');
update PSlot set backlink = 'WS.not.there' where slotname = 'PS.base.a1';
update PSlot set backlink = 'XX.illegal' where slotname = 'PS.base.a1';
update PSlot set slotlink = 'PS.not.there' where slotname = 'PS.base.a1';
update PSlot set slotlink = 'XX.illegal' where slotname = 'PS.base.a1';
insert into HSlot values ('HS', 'base.hub1', 1, '');
insert into HSlot values ('HS', 'base.hub1', 20, '');
delete from HSlot;
insert into IFace values ('IF', 'notthere', 'eth0', '');
insert into IFace values ('IF', 'orion', 'ethernet_interface_name_too_long', '');


--
-- The following tests are unrelated to the scenario outlined above;
-- they merely exercise specific parts of PL/PgSQL
--

--
-- Test recursion, per bug report 7-Sep-01
--
CREATE FUNCTION recursion_test(int,int) RETURNS text AS '
DECLARE rslt text;
BEGIN
    IF $1 <= 0 THEN
        rslt = CAST($2 AS TEXT);
    ELSE
        rslt = CAST($1 AS TEXT) || '','' || recursion_test($1 - 1, $2);
    END IF;
    RETURN rslt;
END;' LANGUAGE 'plpgsql';

SELECT recursion_test(4,3);

--
-- Test the FOUND magic variable
--
CREATE TABLE found_test_tbl (a int);

create function test_found()
  returns boolean as '
  declare
  begin
  insert into found_test_tbl values (1);
  if FOUND then
     insert into found_test_tbl values (2);
  end if;

  update found_test_tbl set a = 100 where a = 1;
  if FOUND then
    insert into found_test_tbl values (3);
  end if;

  delete from found_test_tbl where a = 9999; -- matches no rows
  if not FOUND then
    insert into found_test_tbl values (4);
  end if;

  for i in 1 .. 10 loop
    -- no need to do anything
  end loop;
  if FOUND then
    insert into found_test_tbl values (5);
  end if;

  -- never executes the loop
  for i in 2 .. 1 loop
    -- no need to do anything
  end loop;
  if not FOUND then
    insert into found_test_tbl values (6);
  end if;
  return true;
  end;' language 'plpgsql';

select test_found();
select * from found_test_tbl;

--
-- Test set-returning functions for PL/pgSQL
--

create function test_table_func_rec() returns setof found_test_tbl as '
DECLARE
	rec RECORD;
BEGIN
	FOR rec IN select * from found_test_tbl LOOP
		RETURN NEXT rec;
	END LOOP;
	RETURN;
END;' language 'plpgsql';

select * from test_table_func_rec();

create function test_table_func_row() returns setof found_test_tbl as '
DECLARE
	row found_test_tbl%ROWTYPE;
BEGIN
	FOR row IN select * from found_test_tbl LOOP
		RETURN NEXT row;
	END LOOP;
	RETURN;
END;' language 'plpgsql';

select * from test_table_func_row();

create function test_ret_set_scalar(int,int) returns setof int as '
DECLARE
	i int;
BEGIN
	FOR i IN $1 .. $2 LOOP
		RETURN NEXT i + 1;
	END LOOP;
	RETURN;
END;' language 'plpgsql';

select * from test_ret_set_scalar(1,10);

create function test_ret_set_rec_dyn(int) returns setof record as '
DECLARE
	retval RECORD;
BEGIN
	IF $1 > 10 THEN
		SELECT INTO retval 5, 10, 15;
		RETURN NEXT retval;
		RETURN NEXT retval;
	ELSE
		SELECT INTO retval 50, 5::numeric, ''xxx''::text;
		RETURN NEXT retval;
		RETURN NEXT retval;
	END IF;
	RETURN;
END;' language 'plpgsql';

SELECT * FROM test_ret_set_rec_dyn(1500) AS (a int, b int, c int);
SELECT * FROM test_ret_set_rec_dyn(5) AS (a int, b numeric, c text);

create function test_ret_rec_dyn(int) returns record as '
DECLARE
	retval RECORD;
BEGIN
	IF $1 > 10 THEN
		SELECT INTO retval 5, 10, 15;
		RETURN retval;
	ELSE
		SELECT INTO retval 50, 5::numeric, ''xxx''::text;
		RETURN retval;
	END IF;
END;' language 'plpgsql';

SELECT * FROM test_ret_rec_dyn(1500) AS (a int, b int, c int);
SELECT * FROM test_ret_rec_dyn(5) AS (a int, b numeric, c text);

--
-- Test handling of OUT parameters, including polymorphic cases.
-- Note that RETURN is optional with OUT params; we try both ways.
--

-- wrong way to do it:
create function f1(in i int, out j int) returns int as $$
begin
  return i+1;
end$$ language plpgsql;

create function f1(in i int, out j int) as $$
begin
  j := i+1;
  return;
end$$ language plpgsql;

select f1(42);
select * from f1(42);

create or replace function f1(inout i int) as $$
begin
  i := i+1;
end$$ language plpgsql;

select f1(42);
select * from f1(42);

drop function f1(int);

create function f1(in i int, out j int) returns setof int as $$
begin
  j := i+1;
  return next;
  j := i+2;
  return next;
  return;
end$$ language plpgsql;

select * from f1(42);

drop function f1(int);

create function f1(in i int, out j int, out k text) as $$
begin
  j := i;
  j := j+1;
  k := 'foo';
end$$ language plpgsql;

select f1(42);
select * from f1(42);

drop function f1(int);

create function f1(in i int, out j int, out k text) returns setof record as $$
begin
  j := i+1;
  k := 'foo';
  return next;
  j := j+1;
  k := 'foot';
  return next;
end$$ language plpgsql;

select * from f1(42);

drop function f1(int);

create function duplic(in i anyelement, out j anyelement, out k anyarray) as $$
begin
  j := i;
  k := array[j,j];
  return;
end$$ language plpgsql;

select * from duplic(42);
select * from duplic('foo'::text);

drop function duplic(anyelement);

--
-- test PERFORM
--

create table perform_test (
	a	INT,
	b	INT
);

create function simple_func(int) returns boolean as '
BEGIN
	IF $1 < 20 THEN
		INSERT INTO perform_test VALUES ($1, $1 + 10);
		RETURN TRUE;
	ELSE
		RETURN FALSE;
	END IF;
END;' language 'plpgsql';

create function perform_test_func() returns void as '
BEGIN
	IF FOUND then
		INSERT INTO perform_test VALUES (100, 100);
	END IF;

	PERFORM simple_func(5);

	IF FOUND then
		INSERT INTO perform_test VALUES (100, 100);
	END IF;

	PERFORM simple_func(50);

	IF FOUND then
		INSERT INTO perform_test VALUES (100, 100);
	END IF;

	RETURN;
END;' language 'plpgsql';

SELECT perform_test_func();
SELECT * FROM perform_test;

drop table perform_test;

--
-- Test error trapping
--

create function trap_zero_divide(int) returns int as $$
declare x int;
	sx smallint;
begin
	begin	-- start a subtransaction
		raise notice 'should see this';
		x := 100 / $1;
		raise notice 'should see this only if % <> 0', $1;
		sx := $1;
		raise notice 'should see this only if % fits in smallint', $1;
		if $1 < 0 then
			raise exception '% is less than zero', $1;
		end if;
	exception
		when division_by_zero then
			raise notice 'caught division_by_zero';
			x := -1;
		when NUMERIC_VALUE_OUT_OF_RANGE then
			raise notice 'caught numeric_value_out_of_range';
			x := -2;
	end;
	return x;
end$$ language plpgsql;

select trap_zero_divide(50);
select trap_zero_divide(0);
select trap_zero_divide(100000);
select trap_zero_divide(-100);

create function trap_matching_test(int) returns int as $$
declare x int;
	sx smallint;
	y int;
begin
	begin	-- start a subtransaction
		x := 100 / $1;
		sx := $1;
		select into y unique1 from tenk1 where unique2 =
			(select unique2 from tenk1 b where ten = $1);
	exception
		when data_exception then  -- category match
			raise notice 'caught data_exception';
			x := -1;
		when NUMERIC_VALUE_OUT_OF_RANGE OR CARDINALITY_VIOLATION then
			raise notice 'caught numeric_value_out_of_range or cardinality_violation';
			x := -2;
	end;
	return x;
end$$ language plpgsql;

select trap_matching_test(50);
select trap_matching_test(0);
select trap_matching_test(100000);
select trap_matching_test(1);

create temp table foo (f1 int);

create function blockme() returns int as $$
declare x int;
begin
  x := 1;
  insert into foo values(x);
  begin
    x := x + 1;
    insert into foo values(x);
    -- we assume this will take longer than 1 second:
    select count(*) into x from tenk1 a, tenk1 b, tenk1 c;
  exception
    when others then
      raise notice 'caught others?';
      return -1;
    when query_canceled then
      raise notice 'nyeah nyeah, can''t stop me';
      x := x * 10;
  end;
  insert into foo values(x);
  return x;
end$$ language plpgsql;

set statement_timeout to 1000;

select blockme();

reset statement_timeout;

select * from foo;

-- Test for pass-by-ref values being stored in proper context
create function test_variable_storage() returns text as $$
declare x text;
begin
  x := '1234';
  begin
    x := x || '5678';
    -- force error inside subtransaction SPI context
    perform trap_zero_divide(-100);
  exception
    when others then
      x := x || '9012';
  end;
  return x;
end$$ language plpgsql;

select test_variable_storage();

--
-- test foreign key error trapping
--

create temp table master(f1 int primary key);

create temp table slave(f1 int references master deferrable);

insert into master values(1);
insert into slave values(1);
insert into slave values(2);	-- fails

create function trap_foreign_key(int) returns int as $$
begin
	begin	-- start a subtransaction
		insert into slave values($1);
	exception
		when foreign_key_violation then
			raise notice 'caught foreign_key_violation';
			return 0;
	end;
	return 1;
end$$ language plpgsql;

create function trap_foreign_key_2() returns int as $$
begin
	begin	-- start a subtransaction
		set constraints all immediate;
	exception
		when foreign_key_violation then
			raise notice 'caught foreign_key_violation';
			return 0;
	end;
	return 1;
end$$ language plpgsql;

select trap_foreign_key(1);
select trap_foreign_key(2);	-- detects FK violation

begin;
  set constraints all deferred;
  select trap_foreign_key(2);	-- should not detect FK violation
  savepoint x;