Forum OpenACS Q&A: Re: myfirstpackage: ERROR: duplicate key violates unique constraint

Based on the patch in https://openacs.org/forums/message-view?message_id=1009320 and the discussion in this thread I use now the current version of file which overrides content_type__drop_type.

myfirstpackage-drop.sql:

create or replace function content_type__drop_type(varchar,bool,bool) returns int4 as '
        declare
          drop_type__content_type          alias for $1;
          drop_type__drop_children_p        alias for $2;
          drop_type__drop_table_p          alias for $3;
          table_exists_p                      boolean;
          v_table_name                      varchar;
          is_subclassed_p                  boolean;
          child_rec                        record;
          attr_row                          record;
        begin


          select
            count(*) > 0 into is_subclassed_p
          from
            acs_object_types
          where supertype = drop_type__content_type;


          if drop_type__drop_children_p and is_subclassed_p then

            for child_rec in select
                              object_type
                            from
                              acs_object_types
                            where
                              supertype = drop_type__content_type
            LOOP
              PERFORM content_type__drop_type(child_rec.object_type, ''t'', drop_type__drop_table_p);
            end LOOP;

          end if;

          for attr_row in select
                            attribute_name
                          from
                            acs_attributes
                          where
                            object_type = drop_type__content_type
          LOOP
            PERFORM content_type__drop_attribute(drop_type__content_type,
                                                attr_row.attribute_name,
                                                ''f''
            );
          end LOOP;


          select
            table_exists(lower(table_name)) into table_exists_p
          from
            acs_object_types
          where
            object_type = drop_type__content_type;

          if table_exists_p and drop_type__drop_table_p then
            select
              table_name into v_table_name
            from
              acs_object_types
            where
              object_type = drop_type__content_type;


            execute ''drop table '' || v_table_name || '' cascade'';
          end if;

          PERFORM acs_object_type__drop_type(drop_type__content_type, ''f'');

          return 0;
        end;' language 'plpgsql';

select content_folder__unregister_content_type(-100,'mfp_note','t');

select content_type__drop_type(
          'mfp_note',
          't',
          't'
    );

Sorry for the long posting ;). Comments are appreciated, I am not sure this is correct but as far as I checked all the references to mfp_notes are gone now.

Ok. Seems the better way to do it is to patch the file as indicated in the link in my previous post. Then use the script in the tutorial. Anyhow this works only if no note has been inserted or all notes has been previously deleted.
Btw. My notes-delete.tcl script does not work unless I comment the

set title [item::get_title $item_id]

instruction. Otherwise I get the following exception:

Database operation "0or1row" failed
(exception ERROR, "ERROR: invalid input syntax for integer: "item_id"
")

while executing
"ns_pg_bind 0or1row nsdb0 {

select content_item__get_title(:item_id,:is_live)

}"
...

Maybe someone of you has some hints for me.