From b8423f4d05c75094b7b9d09c6f0bb353acc9be1c Mon Sep 17 00:00:00 2001 From: ckonstanski Date: Sat, 25 May 2024 18:03:25 -0600 Subject: initial commit --- ...contact.get_contact_us_posts_by_id_and_read.sql | 30 ++++++++++++++++++++++ 1 file changed, 30 insertions(+) create mode 100644 sql/functions/contact.get_contact_us_posts_by_id_and_read.sql (limited to 'sql/functions/contact.get_contact_us_posts_by_id_and_read.sql') diff --git a/sql/functions/contact.get_contact_us_posts_by_id_and_read.sql b/sql/functions/contact.get_contact_us_posts_by_id_and_read.sql new file mode 100644 index 0000000..646fe0f --- /dev/null +++ b/sql/functions/contact.get_contact_us_posts_by_id_and_read.sql @@ -0,0 +1,30 @@ +drop function contact.get_contact_us_posts_by_id_and_read ( + p_user_id bigint, + p_read bool +); + +create or replace function contact.get_contact_us_posts_by_id_and_read ( + p_user_id bigint, + p_read bool +) +returns setof contact.contact_us_posts +as $$ +begin + return query + select c.id, + c.first_name, + c.last_name, + c.email, + c.phone, + c.submitted, + c.comments + from contact.contact_us_posts c + left outer join contact.contact_us_posts_read cr + on c.id = cr.contact_us_post_id + where (p_read = false and cr.user_id is null) + or (p_read = true and cr.user_id = p_user_id) + order by c.submitted desc; + + return; +end; +$$ language plpgsql; -- cgit v1.3