How to test PROCEDURES (NOT functions) in PostgreSQL with pgTAP?

[Dimitar Draginov]

Hello,

In pgTAP documentation and examples I see how we can test SQL or functions...

Can somebody help with best practice of testing stored procedures? 

Thanks,

  Dimitar

[David E. Wheeler]

On Dec 11, 2020, at 08:44, Dimitar Draginov <dimitar....@gmail.com> wrote:

> In pgTAP documentation and examples I see how we can test SQL or functions...
>
> Can somebody help with best practice of testing stored procedures?

Are you running into issues that vary from testing functions?

Best,

David

[Dimitar Draginov]

Hi David,

I could not exactly understand your question, but yes I have issues when trying to test stored procedures. I do not have issues with pgTAP functions.

It fails when I try

SELECT ok(my_proc);

I even tried 

SELECT ok(call my_proc);

Please, give me an example how I can test stored procedures?

Good example could be with a stored procedure having one INOUT parameter.

Another challenge could be a stored procedure returning refcursor as OUT parameter.

Thanks,

  Dimitar 

[Dimitar Draginov]

Actually, I tried something with some success. 

All the time I was thinking pgTAP should perform as a wrapper of my procs and it looks like this was my problem.

I've decided to go with sort of opposite approach.

Now you can see pgTAP functions hand in hand with my stored procedures inside the anonymous blocks where I call them.

Here is a script I've prepared and its output. It looks pretty close to what pgTAP targets.

# The Unit testing script with some test data

$ cat TEST_p_check_folder_changed.sql

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

-- Turn off echo and keep things quiet.

\unset ECHO

\set QUIET 1

-- Format the output for nice TAP.

\pset format unaligned

\pset tuples_only true

\pset pager off

-- Revert all changes on failure.

\set ON_ERROR_ROLLBACK 1

\set ON_ERROR_STOP true

BEGIN;

SELECT plan(3);

/*

-- procedure parameters

CREATE OR REPLACE FUNCTION imgowner.pkg_image_folder$p_check_folder_changed(

      folder_type character,

      folder_id character,

  OUT folder_item_count integer,

  OUT max_rowscn numeric,

  OUT rowcount_out imgowner."pkg_image_folder$st_rows_affected"

)

*/

DO $$

DECLARE

  folder_item_count integer;

  max_rowscn numeric;

  rowcount_out imgowner."pkg_image_folder$st_rows_affected";

  v_ok varchar;

BEGIN

  call imgowner.pkg_image_folder$p_check_folder_changed('PSVA ','VA94000150', folder_item_count, max_rowscn, rowcount_out);

  RAISE NOTICE 'folder_item_count: %', folder_item_count;

  RAISE NOTICE 'max_rowscn: %', max_rowscn;

  RAISE NOTICE 'rowcount_out: %', rowcount_out;

  SELECT ok(folder_item_count >= 0,'is folder_item_count > 0') into v_ok;

  RAISE NOTICE '%', v_ok;

  SELECT ok(max_rowscn >= 0,'is max_rowscn > 0') into v_ok;

  RAISE NOTICE '%', v_ok;

  SELECT ok(rowcount_out <= 0,'is rowcount_out > 0') into v_ok;

  RAISE NOTICE '%', v_ok;

END;

$$;

SELECT * FROM finish();

ROLLBACK;

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

# Executing the Unit testing script and its output

$ psql -d svase2

psql (12.2)

Type "help" for help.

svase2=# \i TEST_p_check_folder_changed.sql

1..3

psql:TEST_p_check_folder_changed.sql:45: NOTICE:  folder_item_count: 11

psql:TEST_p_check_folder_changed.sql:45: NOTICE:  max_rowscn: 4655

psql:TEST_p_check_folder_changed.sql:45: NOTICE:  rowcount_out: 1

psql:TEST_p_check_folder_changed.sql:45: NOTICE:  ok 1 - is folder_item_count > 0

psql:TEST_p_check_folder_changed.sql:45: NOTICE:  ok 2 - is max_rowscn > 0

psql:TEST_p_check_folder_changed.sql:45: NOTICE:  not ok 3 - is rowcount_out > 0

# Failed test 3: "is rowcount_out > 0"

# Looks like you failed 1 test of 3

svase2=# exit

Please, review and let me know your suggestions if it can be improved to match real unit testing.

Do I have to remove "psql:TEST_p_check_folder_changed.sql:45: NOTICE:" at the beginning of each row?

Thanks,

  Dimitar

[David E. Wheeler]

On Dec 14, 2020, at 09:17, Dimitar Draginov <dimitar....@gmail.com> wrote:

> Hi David,

👋🏻

> I could not exactly understand your question, but yes I have issues when trying to test stored procedures. I do not have issues with pgTAP functions.
>
> It fails when I try
> SELECT ok(my_proc);
> I even tried
> SELECT ok(call my_proc);

I have not used stored procedures yet, so take my comments for what little they’re worth, but what does my_proc return when you call it? Because ok() just tests that a single value is true. Does my_proc return a single boolean? Does it return anything? Rows?

Best,

David

[David E. Wheeler]

On Dec 15, 2020, at 02:29, Dimitar Draginov <dimitar....@gmail.com> wrote:

> Actually, I tried something with some success.
> All the time I was thinking pgTAP should perform as a wrapper of my procs and it looks like this was my problem.

Ah, glad to hear it.

> /*
> -- procedure parameters
> CREATE OR REPLACE FUNCTION imgowner.pkg_image_folder$p_check_folder_changed(
> folder_type character,
> folder_id character,
> OUT folder_item_count integer,
> OUT max_rowscn numeric,
> OUT rowcount_out imgowner."pkg_image_folder$st_rows_affected"
> )
> */

I’m a little confused, as this is a function, not a stored procedure.

> DO $$
> DECLARE
> folder_item_count integer;
> max_rowscn numeric;
> rowcount_out imgowner."pkg_image_folder$st_rows_affected";
> v_ok varchar;

DO does not return any rows, so it won’t work. You’ll need to write a function and call it, instead.

> Please, review and let me know your suggestions if it can be improved to match real unit testing.
>
> Do I have to remove "psql:TEST_p_check_folder_changed.sql:45: NOTICE:" at the beginning of each row?

I would, as it’s not tap. You can use diag(), instead. Try this (untested, may have syntax errors):


BEGIN;
SELECT plan(4);

CREATE TEMPORARY FUNCTION validate_folder(
) RETURNS SETOF TEXT language plgpsql AS $$

DECLARE
folder_item_count integer;
max_rowscn numeric;
rowcount_out imgowner."pkg_image_folder$st_rows_affected";

BEGIN
RETURN NEXT lives_ok(
$l$ call imgowner.pkg_image_folder$p_check_folder_changed(

'PSVA ','VA94000150', folder_item_count, max_rowscn, rowcount_out
);

$l$, 'Should get no error calling p_check_folder_changed');
RETURN NEXT cmp_ok(folder_item_count, '>' 0, 'Should have folder_item_count > 0');
RETURN NEXT cmp_ok(max_rowscn, '>', 0, 'Should have max_rowscn > 0');
RETURN NEXT cmp_ok(rowcount_out, '>', 0, 'Should have rowcount_out > 0');
END;
$$;

SELECT * FROM validate_folder();

SELECT * FROM finish();
ROLLBACK;

HTH,

David