Postgres analogue to CROSS APPLY in SQL Server

by

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?

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.

More Related Contents:

  1. Query for array elements inside JSON type
  2. Constraint defined DEFERRABLE INITIALLY IMMEDIATE is still DEFERRED?
  3. PL/pgSQL checking if a row exists
  4. Compute percents from SUM() in the same SELECT sql query
  5. Query for element of array in JSON column
  6. How to find a table having a specific column in postgresql
  7. Postgres function returning table not returning data in columns
  8. Check if NULL exists in Postgres array
  9. How to use SQL LIKE condition with multiple values in PostgreSQL?
  10. How to create a new database with the hstore extension already installed?
  11. How to perform a select query in a DO block?
  12. Join a count query on generate_series() and retrieve Null values as ‘0’
  13. PostgreSQL – dynamic value as table name [duplicate]
  14. Computed / calculated / virtual / derived columns in PostgreSQL
  15. What is the difference between single quotes and double quotes in PostgreSQL?
  16. Simulate CREATE DATABASE IF NOT EXISTS for PostgreSQL?
  17. PostgreSQL: Which Datatype should be used for Currency?
  18. Best way to count rows by arbitrary time intervals
  19. PostgreSQL sequence based on another column
  20. Finding similar strings with PostgreSQL quickly
  21. Why do NULL values come first when ordering DESC in a PostgreSQL query?
  22. Slow simple update query on PostgreSQL database with 3 million rows
  23. How do I alter the position of a column in a PostgreSQL database table?
  24. Return setof record (virtual table) from function
  25. Transposing an sql result so that one column goes onto multiple columns
  26. How to convert PostgreSQL 9.4’s jsonb type to float
  27. postgres – where in (list) – column does not exist
  28. incomplete information from query on pg_views [duplicate]
  29. PostgreSQL: Give all permissions to a user on a PostgreSQL database
  30. Referencing current row in FILTER clause of window function

Leave a Comment