plpgsql.sql

postgresql8.3.4源码,开源数据库

  if found then return x; end if;
  return 0;
end$$ language plpgsql stable;

insert into users values('user1');

select sp_id_user('user1');
select sp_id_user('userx');

create function sp_add_user(a_login text) returns int as $$
declare my_id_user int;
begin
  my_id_user = sp_id_user( a_login );
  IF  my_id_user > 0 THEN
    RETURN -1;  -- error code for existing user
  END IF;
  INSERT INTO users ( login ) VALUES ( a_login );
  my_id_user = sp_id_user( a_login );
  IF  my_id_user = 0 THEN
    RETURN -2;  -- error code for insertion failure
  END IF;
  RETURN my_id_user;
end$$ language plpgsql;

select sp_add_user('user1');
select sp_add_user('user2');
select sp_add_user('user2');
select sp_add_user('user3');
select sp_add_user('user3');

drop function sp_add_user(text);
drop function sp_id_user(text);

--
-- tests for refcursors
--
create table rc_test (a int, b int);
copy rc_test from stdin;
5	10
50	100
500	1000
\.

create function return_refcursor(rc refcursor) returns refcursor as $$
begin
    open rc for select a from rc_test;
    return rc;
end
$$ language plpgsql;

create function refcursor_test1(refcursor) returns refcursor as $$
begin
    perform return_refcursor($1);
    return $1;
end
$$ language plpgsql;

begin;

select refcursor_test1('test1');
fetch next in test1;

select refcursor_test1('test2');
fetch all from test2;

commit;

-- should fail
fetch next from test1;

create function refcursor_test2(int, int) returns boolean as $$
declare
    c1 cursor (param1 int, param2 int) for select * from rc_test where a > param1 and b > param2;
    nonsense record;
begin
    open c1($1, $2);
    fetch c1 into nonsense;
    close c1;
    if found then
        return true;
    else
        return false;
    end if;
end
$$ language plpgsql;

select refcursor_test2(20000, 20000) as "Should be false",
       refcursor_test2(20, 20) as "Should be true";

--
-- tests for "raise" processing
--
create function raise_test1(int) returns int as $$
begin
    raise notice 'This message has too many parameters!', $1;
    return $1;
end;
$$ language plpgsql;

select raise_test1(5);

create function raise_test2(int) returns int as $$
begin
    raise notice 'This message has too few parameters: %, %, %', $1, $1;
    return $1;
end;
$$ language plpgsql;

select raise_test2(10);

--
-- reject function definitions that contain malformed SQL queries at
-- compile-time, where possible
--
create function bad_sql1() returns int as $$
declare a int;
begin
    a := 5;
    Johnny Yuma;
    a := 10;
    return a;
end$$ language plpgsql;

create function bad_sql2() returns int as $$
declare r record;
begin
    for r in select I fought the law, the law won LOOP
        raise notice 'in loop';
    end loop;
    return 5;
end;$$ language plpgsql;

-- a RETURN expression is mandatory, except for void-returning
-- functions, where it is not allowed
create function missing_return_expr() returns int as $$
begin
    return ;
end;$$ language plpgsql;

create function void_return_expr() returns void as $$
begin
    return 5;
end;$$ language plpgsql;

-- VOID functions are allowed to omit RETURN
create function void_return_expr() returns void as $$
begin
    perform 2+2;
end;$$ language plpgsql;

select void_return_expr();

-- but ordinary functions are not
create function missing_return_expr() returns int as $$
begin
    perform 2+2;
end;$$ language plpgsql;

select missing_return_expr();

drop function void_return_expr();
drop function missing_return_expr();

--
-- EXECUTE ... INTO test
--

create table eifoo (i integer, y integer);
create type eitype as (i integer, y integer);

create or replace function execute_into_test(varchar) returns record as $$
declare
    _r record;
    _rt eifoo%rowtype;
    _v eitype;
    i int;
    j int;
    k int;
begin
    execute 'insert into '||$1||' values(10,15)';
    execute 'select (row).* from (select row(10,1)::eifoo) s' into _r;
    raise notice '% %', _r.i, _r.y;
    execute 'select * from '||$1||' limit 1' into _rt;
    raise notice '% %', _rt.i, _rt.y;
    execute 'select *, 20 from '||$1||' limit 1' into i, j, k;
    raise notice '% % %', i, j, k;
    execute 'select 1,2' into _v;
    return _v;
end; $$ language plpgsql;

select execute_into_test('eifoo');

drop table eifoo cascade;
drop type eitype cascade;

--
-- SQLSTATE and SQLERRM test
--

create function excpt_test1() returns void as $$
begin
    raise notice '% %', sqlstate, sqlerrm;
end; $$ language plpgsql;
-- should fail: SQLSTATE and SQLERRM are only in defined EXCEPTION
-- blocks
select excpt_test1();

create function excpt_test2() returns void as $$
begin
    begin
        begin
    	    raise notice '% %', sqlstate, sqlerrm;
        end;
    end;
end; $$ language plpgsql;
-- should fail
select excpt_test2();

create function excpt_test3() returns void as $$
begin
    begin
    	raise exception 'user exception';
    exception when others then
	    raise notice 'caught exception % %', sqlstate, sqlerrm;
	    begin
	        raise notice '% %', sqlstate, sqlerrm;
	        perform 10/0;
        exception
            when substring_error then
                -- this exception handler shouldn't be invoked
                raise notice 'unexpected exception: % %', sqlstate, sqlerrm;
	        when division_by_zero then
	            raise notice 'caught exception % %', sqlstate, sqlerrm;
	    end;
	    raise notice '% %', sqlstate, sqlerrm;
    end;
end; $$ language plpgsql;

select excpt_test3();
drop function excpt_test1();
drop function excpt_test2();
drop function excpt_test3();

-- parameters of raise stmt can be expressions
create function raise_exprs() returns void as $$
declare
    a integer[] = '{10,20,30}';
    c varchar = 'xyz';
    i integer;
begin
    i := 2;
    raise notice '%; %; %; %; %; %', a, a[i], c, (select c || 'abc'), row(10,'aaa',NULL,30), NULL;
end;$$ language plpgsql;

select raise_exprs();
drop function raise_exprs();

-- continue statement
create table conttesttbl(idx serial, v integer);
insert into conttesttbl(v) values(10);
insert into conttesttbl(v) values(20);
insert into conttesttbl(v) values(30);
insert into conttesttbl(v) values(40);

create function continue_test1() returns void as $$
declare _i integer = 0; _r record;
begin
  raise notice '---1---';
  loop
    _i := _i + 1;
    raise notice '%', _i;
    continue when _i < 10;
    exit;
  end loop;

  raise notice '---2---';
  <<lbl>>
  loop
    _i := _i - 1;
    loop
      raise notice '%', _i;
      continue lbl when _i > 0;
      exit lbl;
    end loop;
  end loop;

  raise notice '---3---';
  <<the_loop>>
  while _i < 10 loop
    _i := _i + 1;
    continue the_loop when _i % 2 = 0;
    raise notice '%', _i;
  end loop;

  raise notice '---4---';
  for _i in 1..10 loop
    begin
      -- applies to outer loop, not the nested begin block
      continue when _i < 5;
      raise notice '%', _i;
    end;
  end loop;

  raise notice '---5---';
  for _r in select * from conttesttbl loop
    continue when _r.v <= 20;
    raise notice '%', _r.v;
  end loop;

  raise notice '---6---';
  for _r in execute 'select * from conttesttbl' loop
    continue when _r.v <= 20;
    raise notice '%', _r.v;
  end loop;

  raise notice '---7---';
  for _i in 1..3 loop
    raise notice '%', _i;
    continue when _i = 3;
  end loop;

  raise notice '---8---';
  _i := 1;
  while _i <= 3 loop
    raise notice '%', _i;
    _i := _i + 1;
    continue when _i = 3;
  end loop;

  raise notice '---9---';
  for _r in select * from conttesttbl order by v limit 1 loop
    raise notice '%', _r.v;
    continue;
  end loop;

  raise notice '---10---';
  for _r in execute 'select * from conttesttbl order by v limit 1' loop
    raise notice '%', _r.v;
    continue;
  end loop;
end; $$ language plpgsql;

select continue_test1();

-- CONTINUE is only legal inside a loop
create function continue_test2() returns void as $$
begin
    begin
        continue;
    end;
    return;
end;
$$ language plpgsql;

-- should fail
select continue_test2();

-- CONTINUE can't reference the label of a named block
create function continue_test3() returns void as $$
begin
    <<begin_block1>>
    begin
        loop
            continue begin_block1;
        end loop;
    end;
end;
$$ language plpgsql;

-- should fail
select continue_test3();

drop function continue_test1();
drop function continue_test2();
drop function continue_test3();
drop table conttesttbl;

-- verbose end block and end loop
create function end_label1() returns void as $$
<<blbl>>
begin
  <<flbl1>>
  for _i in 1 .. 10 loop
    exit flbl1;
  end loop flbl1;
  <<flbl2>>
  for _i in 1 .. 10 loop
    exit flbl2;
  end loop;
end blbl;
$$ language plpgsql;

select end_label1();
drop function end_label1();

-- should fail: undefined end label
create function end_label2() returns void as $$
begin
  for _i in 1 .. 10 loop
    exit;
  end loop flbl1;
end;
$$ language plpgsql;

-- should fail: end label does not match start label
create function end_label3() returns void as $$
<<outer_label>>
begin
  <<inner_label>>
  for _i in 1 .. 10 loop
    exit;
  end loop outer_label;
end;
$$ language plpgsql;

-- should fail: end label on a block without a start label
create function end_label4() returns void as $$
<<outer_label>>
begin
  for _i in 1 .. 10 loop
    exit;
  end loop outer_label;
end;
$$ language plpgsql;

-- using list of scalars in fori and fore stmts
create function for_vect() returns void as $proc$
<<lbl>>declare a integer; b varchar; c varchar; r record;
begin
  -- fori
  for i in 1 .. 3 loop
    raise notice '%', i;
  end loop;
  -- fore with record var
  for r in select gs as aa, 'BB' as bb, 'CC' as cc from generate_series(1,4) gs loop
    raise notice '% % %', r.aa, r.bb, r.cc;
  end loop;
  -- fore with single scalar
  for a in select gs from generate_series(1,4) gs loop
    raise notice '%', a;
  end loop;
  -- fore with multiple scalars
  for a,b,c in select gs, 'BB','CC' from generate_series(1,4) gs loop
    raise notice '% % %', a, b, c;
  end loop;
  -- using qualified names in fors, fore is enabled, disabled only for fori
  for lbl.a, lbl.b, lbl.c in execute $$select gs, 'bb','cc' from generate_series(1,4) gs$$ loop
    raise notice '% % %', a, b, c;
  end loop;
end;
$proc$ language plpgsql;

select for_vect();

-- regression test: verify that multiple uses of same plpgsql datum within
-- a SQL command all get mapped to the same $n parameter.  The return value
-- of the SELECT is not important, we only care that it doesn't fail with
-- a complaint about an ungrouped column reference.
create function multi_datum_use(p1 int) returns bool as $$
declare
  x int;
  y int;
beg