Adding a check in arguments json_to_set in postgres

Question

So I have

WITH input_students as (select *
        from json_to_recordset(json_data :: json -> 'users')
        as student (organization_id bigint, section text, class text,admission_number text))

Now , I wanna make sure that the organization_id is not "" (if so , null would be preferable as a value),however I am not able to use case when ... then end inside of arguments (ignore it as a beginner's mistake if it is one). I haven't found any other resource to fix the bug of handling an empty string scenario. Any help would be appreciated. I tried doing

WITH input_students as (select *
        from json_to_recordset(json_data :: json -> 'users')
        as student (organization_id bigint, section text, class text,admission_number text))
        insert into students select nullif(a.organization_id,''),a.section,a.class ,a.admission_number,'1',2,3,'true' from input_students a;

but it provides with an error "error": "org.postgresql.util.PSQLException: ERROR: invalid input syntax for integer: \"\"\n Where: PL/pgSQL function hsg_put_user_class_section_v2(text) line 22 at SQL statement" The function is :

create or replace function hsg_put_user_class_section_v2(json_data text)
returns table(org_id bigint,
admission_number text,
status text)
LANGUAGE plpgsql
AS $function$
declare
    error_organization_id_invalid character varying := 'Organization ID is invalid !';
    error_admission_number_invalid character varying := 'Admission Number is invalid !';
    error_user_does_not_exist character varying := 'User does not exist !';
    error_class_not_found character varying := 'Class not found !';
    error_section_not_found character varying := 'Section not found';
    success_message character varying :='Updated Successfully';
begin
CREATE TEMP TABLE students (
        organization_id bigint,
        section text,
        class text,
        admission_number text,
        admission_number_in_hsg text,
        organization_id_in_hsg bigint,
        user_id bigint,
        status text
        )
        ON COMMIT DROP;
    
        WITH input_students as (select *
        from json_to_recordset(json_data :: json -> 'users')
        as student (organization_id bigint, section text, class text,admission_number text))
        insert into students select nullif(a.organization_id,0),a.section,a.class ,a.admission_number,'1',2,3,'true' from input_students a;
        return query select
                s.organization_id,
                s.admission_number,
                s.status
                from students s;
end;
$function$ ;

Your error doesn't make sense with the rest of your question. If you are creating a function, you should put that code in here. However, I would recommend trying to get the query correct before putting it into a function. — Jeremy
– Jeremy, Commented Nov 6, 2020 at 13:14
Its only a part of the entire function . what I eventually return is the temporary table stundents like return query SELECT * FROM students — Ankush Verma
– Ankush Verma, Commented Nov 6, 2020 at 13:54
Yes, I get that, but without the rest of the function, it's not possible to accurately debug your issue. The errors you are getting could be related to PL/pgSQL syntax, not SQL. — Jeremy
– Jeremy, Commented Nov 6, 2020 at 13:58

Laurenz Albe · Accepted Answer · 2020-11-06 14:09:02Z

1

If I understand you right, that should be

WITH input_students as (
   SELECT *
   FROM json_to_recordset(json_data::json -> 'users')
   AS s(organization_id text,
        section text,
        class text,
        admission_number text)
)
INSERT INTO students
SELECT CAST (nullif(a.organization_id,'') AS bigint),
       a.section,
       a.class,
       a.admission_number,
       '1',
       2,
       3,
       'true'
FROM input_students AS a;

edited Nov 6, 2020 at 14:09

answered Nov 6, 2020 at 12:34

Laurenz Albe

257k22 gold badges313 silver badges389 bronze badges

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

4 Comments

Ankush Verma Over a year ago

I tried your solution but it provided with an error , I have updated my question with the solution that I tried.

Ankush Verma Over a year ago

Error :

"error": "org.postgresql.util.PSQLException: ERROR: invalid input syntax for integer: \"\"\n  Where: SQL statement \"WITH input_students as (select *\r\n\t\tfrom json_to_recordset(json_data :: json -> 'users')\r\n\t\tas student (organization_id bigint, section text, class text,admission_number text))\r\n\t\tinsert into students select nullif(a.organization_id,0),a.section,a.class ,a.admission_number,'1',2,3,'true' from input_students a\"\nPL/pgSQL function hsg_put_user_class_section_v2(text) line 22 at SQL statement"

Laurenz Albe Over a year ago

Ah. I see, you have empty strings in the JSON. You could have said so. Then you have to use text in the CTE, see my answer. Note that this will still fail if there are values that are not empty strings and not numbers either, but I cannot guess what your data look like.

Ankush Verma Over a year ago

Yep that checks for empty string .Thank you very much for your help.

Collectives™ on Stack Overflow

Adding a check in arguments json_to_set in postgres

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related