CODEWITHSUNDEEP
postgresqlview
Anji
Anji

Reputation: 57

Join a view to another table

Is it possible to join a view with another table in SQL? If so, how?

I have a query on Oracle db which has specific fields. I need to re create the same query on PostgreSQL but some of the data in the PostgreSQL query are coming from a view... And that view has missing information. It's a pretty complex view, so I don't want to NOT use it for now.

For example, in Oracle I do this:

SELECT
d.dos_id,
trunc(d.dos_creation, 'MM') as Cohorte,
sum(v.ver_etude + v.ver_direct) as encaissé
from t_dossier d
left outer join v_versement v
on v.dos_id = d.dos_id

In the Postgres one, I'm using a view. But the view does not return "dos_id" so I cannot explicitly join v_versement with the view.

Is there a way to force a view to return specific fields at runtime which weren't there when creating the view?

Upvotes: 1

Views: 9170

Answers (2)

Anji
Anji

Reputation: 57

I guess I had not realised that I can actually use the view without creating it...

So I edited the SQL statement that makes up the view, added the fields that I needed and used the code of the view without having to create a new view (creating a new view would mean outsourcing it to another company, which would cost us money..)

Thanks :)

Upvotes: 0

Vao Tsun
Vao Tsun

Reputation: 51446

You can't force it

to return specific fields at runtime which weren't there when creating the view

You can create or replace it with limitation:

https://www.postgresql.org/docs/current/static/sql-createview.html

CREATE OR REPLACE VIEW is similar, but if a view of the same name already exists, it is replaced. The new query must generate the same columns that were generated by the existing view query (that is, the same column names in the same order and with the same data types), but it may add additional columns to the end of the list. The calculations giving rise to the output columns may be completely different.

example:

t=# create view v2 as select now();
CREATE VIEW
Time: 36.488 ms
t=# create or replace view v2 as select now(),current_user;
CREATE VIEW
Time: 8.551 ms
t=# create or replace view v2 as select now()::text,current_user;
ERROR:  cannot change data type of view column "now" from timestamp with time zone to text
Time: 0.430 ms

Upvotes: 3

Related Questions