Postgres Analogue to Cross Apply in SQL Server

Postgres analogue to CROSS APPLY in SQL Server

In Postgres 9.3 or later use a LATERAL join:

SELECT v.col_a, v.col_b, f.*  -- no parentheses, f is a table alias
FROM   v_citizenversions v
LEFT   JOIN LATERAL f_citizen_rec_modified(v.col1, v.col2) f ON true
WHERE  f.col_c = _col_c;

Why LEFT JOIN LATERAL ... ON true?

• Record returned from function has columns concatenated

---

For older versions, there is a very simple way to accomplish what I think you are trying to with a set-returning function (RETURNS TABLE or RETURNS SETOF record OR RETURNS record):

SELECT *, (f_citizen_rec_modified(col1, col2)).*
FROM   v_citizenversions v

The function computes values once for every row of the outer query. If the function returns multiple rows, resulting rows are multiplied accordingly. All parentheses are syntactically required to decompose a row type. The table function could look something like this:

CREATE OR REPLACE FUNCTION f_citizen_rec_modified(_col1 int, _col2 text)
  RETURNS TABLE(col_c integer, col_d text)
  LANGUAGE sql AS
$func$
SELECT s.col_c, s.col_d
FROM   some_tbl s
WHERE  s.col_a = $1
AND    s.col_b = $2
$func$;

You need to wrap this in a subquery or CTE if you want to apply a WHERE clause because the columns are not visible on the same level. (And it's better for performance anyway, because you prevent repeated evaluation for every output column of the function):

SELECT col_a, col_b, (f_row).*
FROM  (
   SELECT col_a, col_b, f_citizen_rec_modified(col1, col2) AS f_row
   FROM   v_citizenversions v
   ) x
WHERE (f_row).col_c = _col_c;

There are several other ways to do this or something similar. It all depends on what you want exactly.

What is the equivalent Syntax for Outer Apply in PostgreSQL

It is a lateral join:

SELECT table1.col1, table1.col2, Supp.ID, Supp.Supplier
FROM SIS_PRS table1 LEFT JOIN LATERAL
     (SELECT ID, SupplierName
      FROM table2
      WHERE table2.ID = table1.SupplierID
      FETCH FIRST 1 ROW ONLY
     ) Supp
     ON true;

However, you can come pretty close in either database with just a correlated subquery:

SELECT table1.col1, table1.col2, table1.SupplierID, 
       (SELECT Name
        FROM table2
        WHERE table2.ID = table1.SupplierID
        FETCH FIRST 1 ROW ONLY
       ) as SupplierName
FROM SIS_PRS table1;

Also note that in both databases, fetching one row with no ORDER BY is suspicious.

Combine postgres function with query

I wrote the answer you found, but there may be a better answer depending on what your function actually returns: one or multiple rows? Assuming one row.

What you have in the question is incorrect either way. The row expression must be enclosed in parentheses to decompose it, else the syntax is ambiguous. It must be:

SELECT (getAdditionalInfoAboutDate(date)).* FROM sampleCalendar;

However, Postgres has a weak spot here. It would evaluate the function multiple times - once for every column in the result. To optimize performance put the function call in a subquery:

SELECT (f_row).*
FROM  (
   SELECT getAdditionalInfoAboutDate(date) AS f_row
   FROM   sampleCalendar
   ) sub;

Or use a LEFT JOIN LATERAL in pg 9.3+

SELECT f_row.*  -- no parentheses here, it's a table alias
FROM   sampleCalendar s
LEFT   JOIN LATERAL getAdditionalInfoAboutDate(s.date) f_row ON true

More details:

• Record returned from function has columns concatenated

SQL for json array in column

To find a key/value pair with a known key, you can use several different methods, using the contains operator is one of them:

select *
from table_name
where the_jsonb_column @> '{"regions": [{"region_attributes": {"Material Type": "wood"}}]}'

The equivalent of the mentioned openjson function (from SQL Server) would be jsonb_each() but that (just like openjson) will only expand the top-level key/value pairs. It doesn't do this recursively.

If you at least know the key is somewhere in the regions array, you can use a JSON/Path expression that iterates over all elements (recursively):

select *
from table_name
where (t.the_jsonb_column -> 'regions') @@ '$[*].** == "wood"'

MS Access CROSS APPLY analogue

I believe I have finally managed to come up with a MS Access query, analogous to the CROSS APPLY variant I had previously posted.

SELECT DP.*
FROM DEVICE_PLACES DP
WHERE DP.ID IN
(
    SELECT TOP 5 ID
    FROM DEVICE_PLACES
    WHERE DeviceID = DP.DeviceID
    ORDER BY [Date] DESC, ID
)

Running, however, the self-join variant over a data set of more than 550,000 rows shows a disastrous performance. It takes more than 4 minutes to reduce the data set to the 5 most recent coordinates for each device.

Sql Table Select from the results of a Postgres function

You seem to want LATERAL JOIN :

SELECT t.*
FROM student s
INNER JOIN student_course sc ON s.id = sc.student_id
INNER JOIN course c ON c.id = sc.course_id
INNER JOIN LATERAL course_assessment_info_by_student_id_and_course_id(s.id, c.id) t ON true

This should do it as well :

select (course_assessment_info_by_student_id_and_course_id(s.id, c.id)).*
from student s
join student_course sc on s.id = sc.student_id
join course c on c.id = sc.course_id;

Transform wide table to long using INNER JOIN LATERAL and postgresql

You have to use a CASE for the valeur column and cast it to a common type (e.g. text), along with a list of parameter names:

with 
  table1(id_profil, no_h, parametre1, parametre2, parametre3) as (
    VALUES (01, 1, 5, 7, 'x1'::text), 
           (01, 2, 7, 78, 'x2'), 
           (02, 1, 5, 7, 'x3')
    ),
  col as (
    unnest('{parametre1,parametre2,parametre3}'::text[])
    )
select t.id_profil, t.no_h, col as parametre,
       case col when 'parametre1' then t.parametre1::text
                when 'parametre2' then t.parametre2::text
                when 'parametre3' then t.parametre3::text
       end as valeur
from col
cross join table1 t
order by 1, 2, 3

Postgres: how can I aggregate a row to report the column name with the highest value?

Use DISTINCT ON (row_id) with ORDER BY row_id, max_value DESC:

SELECT DISTINCT ON (row_id) row_id, max_type, max_value
FROM (
    SELECT
        row_id,
        unnest(ARRAY['type1','type2','type3']) AS max_type,
        unnest(ARRAY[type1,type2,type3]) AS max_value
    FROM a_table
    ) s
ORDER BY row_id, max_value DESC;

 row_id | max_type | max_value 
--------+----------+-----------
 A      | type2    |        44
 B      | type1    |       321
 C      | type2    |       620
(3 rows)

---

Related Topics