cur_line_start=scanorig;

cur_line_num=1;

if (*cur_line_start=='\r')

cur_line_start++;

if (*cur_line_start=='\n')

cur_line_start++;

cur_line_end=strchr(cur_line_start,'\n');

}

end$$ language plpgsql;

select doubledecrement(3); -- fail because of implicit null assignment

ERROR: domain pos_int does not allow null values

CONTEXT: PL/pgSQL function "doubledecrement" line2 during statement block local variable initialization

CONTEXT: PL/pgSQL function "doubledecrement" line3 during statement block local variable initialization

create or replace function doubledecrement(p1 pos_int) returns pos_int as $$

declare v pos_int := 0;

begin

return p1;

end$$ language plpgsql;

select doubledecrement(3); -- fail at initialization assignment

ERROR: value for domain pos_int violates check constraint "pos_int_check"

CONTEXT: PL/pgSQL function "doubledecrement" line2 during statement block local variable initialization

CONTEXT: PL/pgSQL function "doubledecrement" line3 during statement block local variable initialization

create or replace function doubledecrement(p1 pos_int) returns pos_int as $$

declare v pos_int := 1;

begin

ERROR: value for domain pos_int violates check constraint "pos_int_check"

select doubledecrement(1); -- fail at assignment to v

ERROR: value for domain pos_int violates check constraint "pos_int_check"

CONTEXT: PL/pgSQL function "doubledecrement" line3 at assignment

CONTEXT: PL/pgSQL function "doubledecrement" line4 at assignment

select doubledecrement(2); -- fail at return

ERROR: value for domain pos_int violates check constraint "pos_int_check"

CONTEXT: PL/pgSQL function "doubledecrement" while casting return value to function's return type

select myfunc(0);

ERROR: division by zero

CONTEXT: SQL statement "SELECT 1/$1"

PL/pgSQL function "myfunc" line3 at PERFORM

PL/pgSQL function "myfunc" line4 at PERFORM

select current_setting('work_mem');

current_setting

-----------------

select cachebug();

NOTICE: table "temptable" does not exist, skipping

CONTEXT: SQL statement "drop table if exists temptable cascade"

PL/pgSQL function "cachebug" line3 at SQL statement

PL/pgSQL function "cachebug" line4 at SQL statement

NOTICE: 1

NOTICE: 2

NOTICE: 3

select cachebug();

NOTICE: drop cascades to view vv

CONTEXT: SQL statement "drop table if exists temptable cascade"

PL/pgSQL function "cachebug" line3 at SQL statement

PL/pgSQL function "cachebug" line4 at SQL statement

NOTICE: 1

NOTICE: 2

NOTICE: 3

DETAIL: Key (name)=(PF1_1) already exists.

update PSlot set backlink = 'WS.not.there' where slotname = 'PS.base.a1';

ERROR: WS.not.there does not exist

CONTEXT: PL/pgSQL function "tg_backlink_a" line16 at assignment

CONTEXT: PL/pgSQL function "tg_backlink_a" line17 at assignment

update PSlot set backlink = 'XX.illegal' where slotname = 'PS.base.a1';

ERROR: illegal backlink beginning with XX

CONTEXT: PL/pgSQL function "tg_backlink_a" line16 at assignment

CONTEXT: PL/pgSQL function "tg_backlink_a" line17 at assignment

update PSlot set slotlink = 'PS.not.there' where slotname = 'PS.base.a1';

ERROR: PS.not.there does not exist

CONTEXT: PL/pgSQL function "tg_slotlink_a" line16 at assignment

CONTEXT: PL/pgSQL function "tg_slotlink_a" line17 at assignment

update PSlot set slotlink = 'XX.illegal' where slotname = 'PS.base.a1';

ERROR: illegal slotlink beginning with XX

CONTEXT: PL/pgSQL function "tg_slotlink_a" line16 at assignment

CONTEXT: PL/pgSQL function "tg_slotlink_a" line17 at assignment

insert into HSlot values ('HS', 'base.hub1', 1, '');

ERROR: duplicate key value violates unique constraint "hslot_name"

DETAIL: Key (slotname)=(HS.base.hub1.1 ) already exists.

select test_variable_storage();

NOTICE: should see this

CONTEXT: SQL statement "SELECT trap_zero_divide(-100)"

PL/pgSQL function "test_variable_storage" line7 at PERFORM

PL/pgSQL function "test_variable_storage" line8 at PERFORM

NOTICE: should see this only if -100 <> 0

CONTEXT: SQL statement "SELECT trap_zero_divide(-100)"

PL/pgSQL function "test_variable_storage" line7 at PERFORM

PL/pgSQL function "test_variable_storage" line8 at PERFORM

NOTICE: should see this only if -100 fits in smallint

CONTEXT: SQL statement "SELECT trap_zero_divide(-100)"

PL/pgSQL function "test_variable_storage" line7 at PERFORM

PL/pgSQL function "test_variable_storage" line8 at PERFORM

test_variable_storage

-----------------------

123456789012

$$ language plpgsql;

select raise_test1(5);

ERROR: too many parameters specified for RAISE

CONTEXT: PL/pgSQL function "raise_test1" line2 at RAISE

CONTEXT: PL/pgSQL function "raise_test1" line3 at RAISE

create function raise_test2(int) returns int as $$

begin

raise notice 'This message has too few parameters: %, %, %', $1, $1;

$$ language plpgsql;

select raise_test2(10);

ERROR: too few parameters specified for RAISE

CONTEXT: PL/pgSQL function "raise_test2" line2 at RAISE

CONTEXT: PL/pgSQL function "raise_test2" line3 at RAISE

--

-- reject function definitions that contain malformed SQL queries at

-- compile-time, where possible

LINE 1: SELECT sqlstate

^

QUERY: SELECT sqlstate

CONTEXT: PL/pgSQL function "excpt_test1" line2 at RAISE

CONTEXT: PL/pgSQL function "excpt_test1" line3 at RAISE

create function excpt_test2() returns void as $$

begin

begin

LINE 1: SELECT sqlstate

^

QUERY: SELECT sqlstate

CONTEXT: PL/pgSQL function "excpt_test2" line4 at RAISE

CONTEXT: PL/pgSQL function "excpt_test2" line5 at RAISE

create function excpt_test3() returns void as $$

begin

begin

end$$ language plpgsql;

select footest();

ERROR: query returned more than one row

CONTEXT: PL/pgSQL function "footest" line4 at SQL statement

CONTEXT: PL/pgSQL function "footest" line5 at SQL statement

create or replace function footest() returns void as $$

declare x record;

begin

end$$ language plpgsql;

select footest();

ERROR: query returned no rows

CONTEXT: PL/pgSQL function "footest" line4 at SQL statement

CONTEXT: PL/pgSQL function "footest" line5 at SQL statement

create or replace function footest() returns void as $$

declare x record;

begin

end$$ language plpgsql;

select footest();

ERROR: query returned more than one row

CONTEXT: PL/pgSQL function "footest" line4 at SQL statement

CONTEXT: PL/pgSQL function "footest" line5 at SQL statement

create or replace function footest() returns void as $$

declare x record;

begin

end$$ language plpgsql;

select footest();

ERROR: query returned no rows

CONTEXT: PL/pgSQL function "footest" line4 at EXECUTE statement

CONTEXT: PL/pgSQL function "footest" line5 at EXECUTE statement

create or replace function footest() returns void as $$

declare x record;

begin

end$$ language plpgsql;

select footest();

ERROR: query returned more than one row

CONTEXT: PL/pgSQL function "footest" line4 at EXECUTE statement

CONTEXT: PL/pgSQL function "footest" line5 at EXECUTE statement

drop function footest();

-- test scrollable cursor support

create function sc_test() returns setof integer as $$

select * from sc_test(); -- fails because of NO SCROLL specification

ERROR: cursor can only scan forward

HINT: Declare it with SCROLL option to enable backward scan.

CONTEXT: PL/pgSQL function "sc_test" line6 at FETCH

CONTEXT: PL/pgSQL function "sc_test" line7 at FETCH

create or replace function sc_test() returns setof integer as $$

declare

c refcursor;

$$ language plpgsql;

select raise_test();

ERROR: RAISE option already specified: MESSAGE

CONTEXT: PL/pgSQL function "raise_test" line2 at RAISE

CONTEXT: PL/pgSQL function "raise_test" line3 at RAISE

-- conflict on errcode

create or replace function raise_test() returns void as $$

begin

$$ language plpgsql;

select raise_test();

ERROR: RAISE option already specified: ERRCODE

CONTEXT: PL/pgSQL function "raise_test" line2 at RAISE

CONTEXT: PL/pgSQL function "raise_test" line3 at RAISE

-- nothing to re-RAISE

create or replace function raise_test() returns void as $$

begin

select case_test(5); -- fails

ERROR: case not found

HINT: CASE statement is missing ELSE part.

CONTEXT: PL/pgSQL function "case_test" line4 at CASE

CONTEXT: PL/pgSQL function "case_test" line5 at CASE

select case_test(8);

case_test

----------------------

select case_test(13); -- fails

ERROR: case not found

HINT: CASE statement is missing ELSE part.

CONTEXT: PL/pgSQL function "case_test" line4 at CASE

CONTEXT: PL/pgSQL function "case_test" line5 at CASE

create or replace function catch() returns void as $$

begin

raise notice '%', case_test(6);

^

HINT: Use the escape string syntax for backslashes, e.g., E'\\'.

QUERY: SELECT 'foo\\bar\041baz'

CONTEXT: PL/pgSQL function "strtest" line3 at RETURN

CONTEXT: PL/pgSQL function "strtest" line4 at RETURN

strtest

-------------

foo\bar!baz

LINE 1: SELECT rtrim(roomno) AS roomno, foo FROM Room ORDER BY roomn...

^

QUERY: SELECT rtrim(roomno) AS roomno, foo FROM Room ORDER BY roomno

CONTEXT: PL/pgSQL function "inline_code_block" line3 at FOR over SELECT rows

CONTEXT: PL/pgSQL function "inline_code_block" line4 at FOR over SELECT rows

-- Check variable scoping -- a var is not available in its own or prior

-- default expressions.

create function scope_test() returns int as $$

^

DETAIL: It could refer to either a PL/pgSQL variable or a table column.

QUERY: select q1,q2 from int8_tbl

CONTEXT: PL/pgSQL function "conflict_test" line4 at FOR over SELECT rows

CONTEXT: PL/pgSQL function "conflict_test" line5 at FOR over SELECT rows

create or replace function conflict_test() returns setof int8_tbl as $$

#variable_conflict use_variable

declare r record;