Forum OpenACS Q&A: data model for "notes" errors

[service2@kmdb postgresql]$ psql -f notes-create.sql
psql:notes-create.sql:9: ERROR: syntax error at or near "acs_object_type" at character 11
psql:notes-create.sql:10: WARNING: there is no transaction in progress
COMMIT
psql:notes-create.sql:12: ERROR: syntax error at or near "/" at character 1
psql:notes-create.sql:15: ERROR: syntax error at or near "acs_attributes" at character 19
psql:notes-create.sql:23: ERROR: syntax error at or near "attr_id" at character 9
psql:notes-create.sql:31: ERROR: syntax error at or near "attr_id" at character 3
psql:notes-create.sql:32: WARNING: there is no transaction in progress
COMMIT
psql:notes-create.sql:34: ERROR: syntax error at or near "/" at character 1
psql:notes-create.sql:57: ERROR: syntax error at or near ")" at character 210
psql:notes-create.sql:61: ERROR: syntax error at or near "procedure" at character 3
psql:notes-create.sql:62: ERROR: syntax error at or near "note" at character 5
psql:notes-create.sql:83: ERROR: syntax error at or near "/" at character 1
psql:notes-create.sql:92: ERROR: syntax error at or near "v_note_id" at character 14
psql:notes-create.sql:97: ERROR: relation "notes" does not exist
psql:notes-create.sql:99: ERROR: syntax error at or near "return" at character 6
psql:notes-create.sql:100: ERROR: syntax error at or near "new" at character 7
psql:notes-create.sql:108: ERROR: syntax error at or near "procedure" at character 3
psql:notes-create.sql:110: ERROR: syntax error at or near "acs_object" at character 5
psql:notes-create.sql:111: ERROR: syntax error at or near "delete" at character 7
psql:notes-create.sql:113: ERROR: syntax error at or near "note" at character 5
psql:notes-create.sql:115: ERROR: syntax error at or near "/" at character 1
[service2@kmdb postgresql]$

sample data model for openACS documentation Notes

begin
acs_object_type.create_type (
supertype => 'acs_object',
object_type => 'note',
pretty_name => 'Note',
pretty_plural => 'Notes',
table_name => 'NOTES',
id_column => 'NOTE_ID',
);
end;
/
show errors;

declare
attr_id acs_attributes.attribute_id%TYPE;
begin
attr_id := acs_attribute.create_attribute (
object_type => 'note',
attribute_name => 'TITLE',
pretty_name => 'Title',
pretty_plural => 'Titles',
datatype => 'string',
);

attr_id := acs_attribute.create_attribute (
object_type => 'note',
attribute_name => 'BODY',
pretty_name => 'Body',
pretty_plural => 'Bodies',
datatype => 'string',
);
end;
/
show errors;

create table notes (
note_id integer references acs_objects(object_id) primary key,
owner_id integer references users(user_id),
title varchar(255) not null,
body varchar(1024),
)

create or replace package note
as
function new (
note_id in notes.note_id%TYPE default null,
owner_id in notes.owner_id%TYPE default null,
title in notes.title%TYPE,
body in notes.body%TYPE,
object_type in acs_object_types.object_type%TYPE default 'note',
creation_date in acs_objects.creation_date%TYPE
default sysdate,
creation_user in acs_objects.creation_user%TYPE
default null,
creation_ip in acs_objects.creation_ip%TYPE default null,
context_id in acs_objects.context_id%TYPE default null,
) return notes.note_id%TYPE;

procedure delete (
note_id in notes.note_id%TYPE
);
end note;
/
show errors

create or replace package body note
as

function new (
note_id in notes.note_id%TYPE default null,
owner_id in notes.owner_id%TYPE default null,
title in notes.title%TYPE,
body in notes.body%TYPE,
object_type in acs_object_types.object_type%TYPE default 'note',
creation_date in acs_objects.creation_date%TYPE
default sysdate,
creation_user in acs_objects.creation_user%TYPE
default null,
creation_ip in acs_objects.creation_ip%TYPE default null,
context_id in acs_objects.context_id%TYPE default null
) return notes.note_id%TYPE
is
v_note_id integer;
begin
v_note_id := acs_object.new (
object_id => note_id,
object_type => object_type,
creation_date => creation_date,
creation_user => creation_user,
creation_ip => creation_ip,
context_id => context_id
);

insert into notes
(note_id, owner_id, title, body)
values
(v_note_id, owner_id, title, body);

return v_note_id;
end new;
procedure delete (
note_id in notes.note_id%TYPE
)
is
begin
delete from notes
where note_id = note.delete.note_id;

acs_object.del(note_id);
end delete;

end note;
/
show errors;