CODEWITHSUNDEEP
sqlpostgresql
Maksym Rybalkin
Maksym Rybalkin

Reputation: 545

Insert after TRUE in condition in PostgreSQL

I need to insert my values if only they are not presented in my table. I wrote the function:

    do
    $$
        declare
            v_video_config_bundle_id bigint;
            v_are_records_exist boolean;
        begin
            select id from config_bundle into v_video_config_bundle_id where code = 'video';
        
            select count(id) > 0 from config_bundle into v_are_records_exist
            where config_bundle_id = v_video_config_bundle_id
            and preference = 'true' and amount = 0 and repeatability in (1,7,14,21,30,45) and format='day';
    
            case
            when (v_are_records_exist = false) then
                insert into config_plan(config_bundle_id, amount, repeatability, format, payment_amount, preference_type, preference, trial, weight, status, is_default)
                values (v_video_config_bundle_id, 0, 7, 'day', 0, 'personal', true, false, 2, 'ACTIVE', false),
                       (v_video_config_bundle_id, 0, 14, 'day', 0, 'personal', true, false, 2, 'ACTIVE', false),
                       (v_video_config_bundle_id, 0, 21, 'day', 0, 'personal', true, false, 2, 'ACTIVE', false);
            end;
        end;
    $$

But I still get an exception ERROR:

syntax error at or near ";"
  Position: 1420

How to fix it?

Upvotes: 0

Views: 48

Answers (1)

Belayer
Belayer

Reputation: 14936

Let SQL make all decisions; put all the determination logic into a single SQL statement. You can do this by converting the filtering logic into NOT EXISTS (SELECT ... structure. So something like:

insert into config_plan(config_bundle_id, amount, repeatability, format, payment_amount, preference_type, preference, trial, weight, status, is_default)
    with new_config ( amount, repeatability, format, payment_amount, preference_type, preference, trial, weight, status, is_default) as 
         ( values ( 0,  7, 'day', 0, 'personal', true, false, 2, 'ACTIVE', false),
                  ( 0, 14, 'day', 0, 'personal', true, false, 2, 'ACTIVE', false),
                  ( 0, 21, 'day', 0, 'personal', true, false, 2, 'ACTIVE', false)
         ) 
    select amount, repeatability, format, payment_amount, preference_type, preference, trial, weight, status, is_default 
      from new_config nc
    where not exists ( select null  
                         from config_plan cp 
                        where (cp.preference, cp.amount , cp.repeatability ,cp.format) = 
                              (nc.preference, nc.amount , nc.repeatability ,nc.format) 
                     ) ;

The above is not tested as you did not supply table description and sample data. However, see here for an example of the technique.

Upvotes: 1

Related Questions