How to create a view with inner join with variables in PostgreSQL?

Question

I am using PostgreSQL DB. I need to create a view which consists of a query with joins on multiple tables, but I am stuck at a point. Please read the below table definition and the Query which I have created.

Tables:

Students
---------
id -> UUID
full_name -> VARCHAR
email_id -> VARCHAR
location -> VARCHAR
created_at -> timestamp
modified_at -> timestamp

Subjects
--------
id -> UUID
title -> VARCHAR
classroom -> VARCHAR
created_at -> TIMESTAMP


Applications
------------
id -> UUID
student_id -> FOREIGN KEY (Students.id)
subject_id -> FOREIGN KEY (Subjects.id)
verified -> BOOLEAN
application_response -> JSON
status_id -> FOREIGN KEY (ApplicationStatus.id)
created_at -> TIMESTAMP

ApplicationStatus
-----------------
id -> UUID
application_status -> VARCHAR
category -> VARCHAR

Scores
-------
student_id -> FOREIGN KEY (Students.id)
subject_id -> FOREIGN KEY (Subjects.id)
score -> NUMERIC

Following is the SQL query which I have created:

create or replace view testing_list_view as 
select c.id as student_id,
a.subject_id as subject_uid,
c.full_name, 
c.email_id,  
c.created_at, 
p.score, 
s.application_status,
a.verified,
a.application_response
from students c
inner join scores p
on p.student_id=c.id and p.subject_id = 'ff342ada-f5gb-44fb-bdth-44e3n59f5448'
inner join applications a
on a.student_id = c.id and a.subject_id= 'ff342ada-f5gb-44fb-bdth-44e3n59f5448'
inner join applicationstatus s 
on s.id = a.status_id
where c.id in 
(select student_id from applications where subject_id='ff342ada-f5gb-44fb-bdth-44e3n59f5448')

Here, I am getting the list of students for the given subject_id along with the scores, application_status and some other fields for which I need to do joins.

My requirement is to make a view for this query so that I pass only the subject_id to the view (select * from testing_list_view where subject_uid='ff342ada-f5gb-44fb-bdth-44e3n59f5448') and I will get a list of the students who have applied to the given subject. But, I am stuck here as in the above join query, subject_id is required in multiple times in the join clauses and is hard-coded. So, I am unable to make it a view.

I was thinking if some kind of variable exists using which, in the join clauses, I will use the variable in the required clauses and which querying the view I will pass it the value (subject_id).

Please let me know if any more clarification is needed.

Views can't have parameters, but you can create a SQL function (`returns table (...)` ) that takes a parameter as an input and simply returns the result of the query — , Feb 28 '19 at 11:01
look at answers here - https://stackoverflow.com/questions/11401749/pass-in-where-parameters-to-postgresql-view — JosMac, Feb 28 '19 at 11:04
@JosMac Looks relevant to my problem. Let me try the solution of that question. — amulya349, Feb 28 '19 at 12:10

Laurenz Albe · Answer 1 · 2019-03-01T11:42:46.373

2

You should define the view without the WHERE clause and all the other restrictions on subject_id:

create or replace view testing_list_view as 
select c.id as student_id,
       a.subject_id as subject_uid,
       c.full_name, 
       c.email_id,  
       c.created_at, 
       p.score, 
       s.application_status,
       a.verified,
       a.application_response
from students c
   inner join scores p
      on p.student_id=c.id
   inner join applications a
      on a.student_id = c.id and a.subject_id = p.subject_id
   inner join applicationstatus s 
      on s.id = a.status_id;

You specify the condition when you use the view, e.g.

SELECT * FROM testing_list_view
WHERE subject_uid = 'ff342ada-f5gb-44fb-bdth-44e3n59f5448';

edited Mar 01 '19 at 11:42

answered Feb 28 '19 at 11:30

Laurenz Albe

209,280
17
206
263

Thanks for your answer. I could not understand the answer. Can you please elaborate? – amulya349 Feb 28 '19 at 12:09
I have elaborated by adding the view definition I envision. – Laurenz Albe Feb 28 '19 at 13:16
When I ran the query you have given, I got an error - SQL Error [42702]: ERROR: column reference "subject_id" is ambiguous. It is because subject_id column is a foreign key in both applications and scores tables. That's why it is giving that error. – amulya349 Feb 28 '19 at 13:34
Which query? The column `subject_id` in the view definition is qualified and cannot be ambiguous. – Laurenz Albe Feb 28 '19 at 14:13
There was a mistake in my query that's why error is coming. Well, the query ran successfully. But, the results are not correct. Because, When I ran my original query (in the question) it is giving a count of 215 (which is correct). But, when I created a view without subject_id and which querying, I used subject_id, it gave me a result count of 2150. – amulya349 Mar 01 '19 at 11:16
Probably, It's doing some sort of cross join somewhere I guess. because in the application table, that much of students are not present for the given subject_id. – amulya349 Mar 01 '19 at 11:17
Sorry. I forgot a join condition. Try again. – Laurenz Albe Mar 01 '19 at 11:43

How to create a view with inner join with variables in PostgreSQL?

1 Answers1