Check upsert function is insert the data or update

[Sagar Pachore]

Check upsert function is insert the data or update using pgTAP

please help
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$
;

Thanks

[David E. Wheeler]

On Jan 6, 2022, at 02:14, Sagar Pachore <sagar....@thinkitive.com> wrote:

> Check upsert function is insert the data or update using pgTAP
> please help

Hi. This is not a general Postgres support community. I suggest you ask on the Postgres mail list, IRC, Slack, or even StackOverflow. You’ll find details here:

https://www.postgresql.org/community/

Best,

David