to test function in postgres

[Sagar Pachore]

how to test function execution in postgres using pgTAP

[David E. Wheeler]

Hello Sagar,

On Jan 10, 2022, at 08:25, Sagar Pachore <sagar....@thinkitive.com> wrote:

> how to test function execution in postgres using pgTAP

There are a number of ways. Do You have a particular problem you’re having trouble figuring out how to test?

Best,

David

[Sagar Pachore]

i used SELECT ok( :boolean, :description ); for the functions who returns boolesn value.
but i confused to test a function who returns table(interger,  response jsonb)

[David E. Wheeler]

On Jan 11, 2022, at 03:12, Sagar Pachore <sagar....@thinkitive.com> wrote:

> i used SELECT ok( :boolean, :description ); for the functions who returns boolesn value.
> but i confused to test a function who returns table(interger, response jsonb)

I see. You can use results_eq() or bag_eq() for something like that:

https://pgxn.org/dist/pgtap/doc/pgtap.html#Can.You.Relate.

So something like

SELECT bag_eq(
$$ SELECT * FROM my_table_returning_function() $$,
$$ VALUES (1, '{"foo": true}'), (2, '{"bar": false}') $$,
'Should get expected values from my_table_returning_function()'
);

HTH,

David

[Sagar Pachore]

I have a function like 
CREATE OR REPLACE FUNCTION testdb.upsert_course_details(studentdetails jsonb, studentresponse jsonb)
 RETURNS jsonb
 LANGUAGE sql
AS $function$
          INSERT INTO testdb.test(
        rollno,
        course_name,
        address
        )
        SELECT
        (e ->> 'rollno')::integer,
        e ->> 'course_name',
        e ->> 'address'
        FROM jsonb($1) e
        ON CONFLICT
    ON CONSTRAINT test_pkey  
        DO UPDATE
        SET
        rollno = EXCLUDED.rollno,
    course_name = EXCLUDED.course_name,
    address = EXCLUDED.address;
   
    INSERT INTO testdb.test2(
    course_id,
    course_name,
    rollno
    )
    SELECT
    ($2 ->> 'course_id')::integer,
    $1 ->> 'course_name',
    t.rollno
    FROM testdb.test t
    WHERE t.rollno = ($1 ->> 'rollno')::integer
    ON CONFLICT
    ON CONSTRAINT test2_pkey
    DO UPDATE
    SET
    course_id = EXCLUDED.course_id,
    course_name = EXCLUDED.course_name,
    rollno = EXCLUDED.rollno;
   
    SELECT
        jsonb_build_object(
        'course_name', ts.course_name,
        'roll_no', ts.rollno,
        'address', ts.address
        )
        FROM testdb.test ts
        where ts.rollno = ($1->>'rollno')::integer;
$function$
;
i have tried 
begin;
  select plan(2);
  select pass('ok');
 SELECT results_eq($$ SELECT * FROM testdb.upsert_course_details('{"rollno":10, "course_name": "javascript", "address": "147 street"}'::jsonb, '{"course_id": 1015}'::jsonb) $$,
$$ VALUES ('{"address": "147 street"}'), ('{"roll_no": 10}'), ('{"course_name": "JAVASCRIPT"}') $$,
'Should get expected values'
);
  select * from finish();
rollback;

but getting error while testing
not ok 2 - Should get expected values
# Failed test 2: "Should get expected values"
#     Number of columns or their types differ between the queries:
#         have: ("{""address"": ""147 street"", ""roll_no"": 10, ""course_name"": ""JAVASCRIPT""}")
#         want: ("{""address"": ""147 street""}")

[David E. Wheeler]

On Jan 12, 2022, at 01:37, Sagar Pachore <sagar....@thinkitive.com> wrote:

> begin;
> select plan(2);
> select pass('ok');
> SELECT results_eq($$ SELECT * FROM testdb.upsert_course_details('{"rollno":10, "course_name": "javascript", "address": "147 street"}'::jsonb, '{"course_id": 1015}'::jsonb) $$,
> $$ VALUES ('{"address": "147 street"}'), ('{"roll_no": 10}'), ('{"course_name": "JAVASCRIPT"}') $$,
> 'Should get expected values'
> );
> select * from finish();
> rollback;
>
> but getting error while testing
> not ok 2 - Should get expected values
> # Failed test 2: "Should get expected values"
> # Number of columns or their types differ between the queries:
> # have: ("{""address"": ""147 street"", ""roll_no"": 10, ""course_name"": ""JAVASCRIPT""}")
> # want: ("{""address"": ""147 street""}")

In VALUES syntax, each set of parentheses are a single record, so write it like this:

$$ VALUES ('{"address": "147 street", {"roll_no": 10}', "course_name": "JAVASCRIPT"}') $$,

HTH,

David