1

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:

  1. Why the "query has no destination for result data" message? I thought PERFORM was meant to prevent that
  2. 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.

1
  • what happens if you remove returning _id from insert statement?.. Commented Jun 18, 2016 at 5:34

1 Answer 1

2

Q1

I think this it returning _id bit that gives an exception. because INSERT ... RETURNING should end up with column list, not variable name. If you want inserted rows column id value to _id variable - change it to returning id INTO _id.

Q2

I belive error happens in the function

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.