I'm moving an existing Sybase system to PostgreSQL and am having trouble with testing my functions. For example I have the following code
drop function if exists contract_line_ins(bigint, bigint, char(10), date, date, numeric(18, 0), numeric(18, 0), integer, bigint);
create function contract_line_ins (
in _contract_id bigint,
in _product_id bigint,
in _number char(10),
in _start_date date,
in _end_date date,
in _history_access numeric(18, 0),
in _subscription_type numeric(18, 0),
inout _rows_updated integer,
inout _id bigint
)
as $$
declare
_locus int = 1; -- Set this in code to indicate current executing statement in exception
_at text; -- Used in exception handling
begin
insert into contract_line(contract_id, product_id, number, start_date, end_date, history_access, subscription_type)
values (_contract_id, _product_id, _number, _start_date, _end_date, _history_access, _subscription_type)
returning _id;
get diagnostics _rows_updated = row_count;
-- Exception handling
exception when others then
get stacked diagnostics _at = PG_EXCEPTION_CONTEXT;
raise notice E'EXCEPTION\nError: %\nMessage: %\nLocus: %\nAt: %', SQLSTATE, SQLERRM, _locus, _at;
end;
$$ language plpgsql;
I appreciate that having both return values is superflous in this case but I have been asked not to change parameters unless absolutely necessary.
I wrote the following test code
do language plpgsql $$
declare
contract_id bigint = 1;
product_id bigint = 2;
number char(10) = 'CONTRACT';
start_date date = '20160101';
end_date date = '20161231';
history_access numeric(18, 0) = 3;
subscription_type numeric(18, 0) = 4;
rows_updated int;
id bigint;
begin
perform contract_line_ins(contract_id, product_id, number, start_date, end_date, history_access, subscription_type, rows_updated, id);
raise notice E'row count % id %', rows_updated, id;
end
$$
When I execute this test I get the following:
[2016-06-18 05:55:17] EXCEPTION
Error: 42601
Message: query has no destination for result data
Locus: 1
At: PL/pgSQL function contract_line_ins(bigint,bigint,character,date,date,numeric,numeric,integer,bigint) line 7 at SQL statement
SQL statement "SELECT contract_line_ins(contract_id, product_id, number, start_date, end_date, history_access, subscription_type, rows_updated, id)"
PL/pgSQL function inline_code_block line 13 at PERFORM
[2016-06-18 05:55:17] row count <NULL> id <NULL>
[2016-06-18 05:55:17] completed in 43ms
What I don't understand is:
- Why the "query has no destination for result data" message? I thought PERFORM was meant to prevent that
- The exception is happening in my test code but is being raised in the function. I would have thought the function would only report exception that happened in its own body. Why is this happening?
Although I have found the PostgreSQL documentation excellent I just can't find out how to do this properly.
Any suggestions gratefully received.
returning _idfrom insert statement?..