CODEWITHSUNDEEP
postgresqljoinlogicinformixouter-join
itsme
itsme

Reputation: 23

PostgreSQL OUTER join logic

I have a pretty old informix procedure which I would like to use in my new PostgreSQL db. I'm new to Postgres and I feel like the joins are pretty different.

This is a part of my old Informix code:

CREATE PROCEDURE mw_getsvid(bid INT)
RETURNING INT;  

DEFINE aid INT;
SELECT a.id INTO aid
    FROM cerberus c, delphi d,
        OUTER (emilia e, fragile f)
    WHERE c.id = [...]

    RETURN aid;

END PROCEDURE;

so what I am trying to do is that c outer joins with e or f. or d outer joins with e or f.

I would be pretty happy if anyone could send me his ideas or a simillar example.

Upvotes: 1

Views: 71

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246558

You'll have to use the SQL standard join syntax with PostgreSQL:

FROM cerberus c
   JOIN delphi d ON d.col1 = c.col2
   LEFT JOIN emilia e ON e.col3 = c.col4
   LEFT JOIN fragile f ON f.col5 = c.col6 AND f.col7 = d.col8

Joins are left associative, but you can use parentheses to override that. Of course the order in which you write doen joins is not necessarily the order in which they are executed.

See the documentation for details. This answer also has interesting information.

Upvotes: 2

Related Questions