got contact info working

5 files changed, 65 insertions, 15 deletions

diff --git a/sql/functions/contact.insert_contact_us_post.sql b/sql/functions/contact.insert_contact_us_post.sql
index 739f084..7ca4495 100644
--- a/sql/functions/contact.insert_contact_us_post.sql
+++ b/sql/functions/contact.insert_contact_us_post.sql
@@ -30,15 +30,7 @@ begin
         p_email,
         p_phone,
         p_comments
-    );
-
-    select id into l_id
-    from contact.contact_us_posts
-    where first_name = p_first_name
-        and last_name = p_last_name
-        and email = p_email
-        and phone = p_phone
-        and comments = p_comments;
+    ) returning id into l_id;
 
     return l_id;
 end;
diff --git a/sql/functions/resume.insert_contactinfo.sql b/sql/functions/resume.insert_contactinfo.sql
new file mode 100644
index 0000000..428e69b
--- /dev/null
+++ b/sql/functions/resume.insert_contactinfo.sql
@@ -0,0 +1,45 @@
+drop function resume.insert_contactinfo (
+    p_user_id int,
+    p_state_id bigint,
+    p_visastatus_id bigint,
+    p_address character varying,
+    p_city character varying,
+    p_phone character varying,
+    p_email character varying
+);
+
+create or replace function resume.insert_contactinfo (
+    p_user_id int,
+    p_state_id bigint,
+    p_visastatus_id bigint,
+    p_address character varying,
+    p_city character varying,
+    p_phone character varying,
+    p_email character varying
+)
+returns bigint
+as $$
+declare
+  l_id bigint;
+begin
+    insert into resume.contactinfo (
+        user_id,
+        state_id,
+        visastatus_id,
+        address,
+        city,
+        phone,
+        email
+    ) values (
+        p_user_id,
+        p_state_id,
+        p_visastatus_id,
+        p_address,
+        p_city,
+        p_phone,
+        p_email
+    ) returning id into l_id;
+
+    return l_id;
+end;
+$$ language plpgsql;
diff --git a/sql/functions/resume.insert_resume.sql b/sql/functions/resume.insert_resume.sql
index eb56897..99ac786 100644
--- a/sql/functions/resume.insert_resume.sql
+++ b/sql/functions/resume.insert_resume.sql
@@ -18,12 +18,7 @@ begin
     ) values (
         p_user_id,
         p_name
-    );
-
-    select id into l_id
-    from resume.resume
-    where user_id = p_user_id
-        and name = p_name;
+    ) returning id into l_id;
 
     return l_id;
 end;
diff --git a/sql/tables/resume.contactinfo.sql b/sql/tables/resume.contactinfo.sql
index 9290da0..8523270 100644
--- a/sql/tables/resume.contactinfo.sql
+++ b/sql/tables/resume.contactinfo.sql
@@ -2,6 +2,7 @@ drop table resume.contactinfo cascade;
 
 create table resume.contactinfo (
     id serial8 primary key,
+    user_id bigint not null references auth.users(id),
     address character varying(255) not null,
     city character varying(255) not null,
     state_id bigint not null references resume.states(id),
diff --git a/sql/views/resume.contactinfo_v.sql b/sql/views/resume.contactinfo_v.sql
new file mode 100644
index 0000000..4c4eb79
--- /dev/null
+++ b/sql/views/resume.contactinfo_v.sql
@@ -0,0 +1,17 @@
+drop view resume.contactinfo_v cascade;
+
+create or replace view resume.contactinfo_v as
+select c.id,
+    c.user_id,
+    c.address,
+    c.city,
+    c.state_id,
+    s.state as state_name,
+    s.abbr as state_abbr,
+    c.phone,
+    c.email,
+    c.visastatus_id,
+    v.status as visastatus
+from resume.contactinfo c
+inner join resume.states s on c.state_id = s.id
+inner join resume.visastatus v on c.visastatus_id = v.id;