Function returning table in WHERE clause, how to use result of query as parameter?

Question

I've got a PL/pgSQL function that return a table whose definition is as follows:

CREATE OR REPLACE FUNCTION get_movement_aggregation(movement_table varchar,place_table varchar,geom varchar,point_gid varchar, radius numeric DEFAULT 20000, inArea geometry DEFAULT NULL)
RETURNS TABLE(place_i integer) AS
-- actual code here

Now I would like to use this function by using a geometry (the inArea parameter) that I get from another table. When I try the following query:

WITH x AS (
  SELECT wkb_geometry from tha_adm1 
  WHERE name_1 = 'Ubon Ratchathani' LIMIT 1
)
SELECT g.* 
FROM x,
get_movement_aggregation('movements','places','geom','place_id',14000,x.wkb_geometry)

I get the following error:

ERROR: function expression in FROM cannot refer to other relations of same query level

It does make sense as I indeed refer to x in both parts of the FROMclause.

The subquery returns the following:

+----------------------------------+
|   |  wkb_geometry                |
|   |  geometry(MultiPolygon,4326) |
+---+------------------------------+
| 1 | some very long number here   |
+---+------------------------------+

And I just use this parameter to filter the list of records I am working with in the function. One solution would be to create a temporary table with the filtered records and feed to the function but if there is a way to do that with a subquery, that would be great.

Thank you in advance for your answers.

Might actually be similar to this question: stackoverflow.com/questions/6687643/… — Julien Bourdon
– Julien Bourdon, Commented Jan 28, 2013 at 12:47

vyegorov · Accepted Answer · 2013-01-29 07:53:14Z

2

Why not this:

WITH x AS (
  SELECT wkb_geometry from tha_adm1 
  WHERE name_1 = 'Ubon Ratchathani' LIMIT 1
)
SELECT 
get_movement_aggregation('movements','places',
                         'geom','place_id',14000,x2.wkb_geometry)
FROM (SELECT * FROM x) as x2

Or a simpler form, without the WITH clause:

SELECT 
get_movement_aggregation('movements','places',
                         'geom','place_id',14000,x2.wkb_geometry)
FROM (SELECT wkb_geometry from tha_adm1 
  WHERE name_1 = 'Ubon Ratchathani' LIMIT 1) as x2

Or if you prefer to have explicit fields rather than a function call in the final select list:

SELECT g.* FROM 
 (SELECT get_movement_aggregation('movements','places',
                                  'geom','place_id',14000,x2.wkb_geometry)
    FROM (SELECT wkb_geometry from tha_adm1 
           WHERE name_1 = 'Ubon Ratchathani' LIMIT 1) as x2
 ) as g;

edited Jan 29, 2013 at 7:53

vyegorov

23k7 gold badges61 silver badges76 bronze badges

answered Jan 28, 2013 at 17:34

Daniel Vérité

62.3k16 gold badges134 silver badges160 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Julien Bourdon Over a year ago

Answer accepted. So the trick was to put the function call in the SELECT... Was not really familiar with functions returning tables. Merci mille fois!!!

vyegorov · Accepted Answer · 2013-01-28 20:08:10Z

1

This form of the query is called LATERAL in the SQL standard. Support for such queries will be available in PostgreSQL 9.3, you can find an excellent review here by @depesz.

Before that you will have to use subqueries, @Daniel outlined a bunch of possibilities.

answered Jan 28, 2013 at 20:08

vyegorov

23k7 gold badges61 silver badges76 bronze badges

1 Comment

Julien Bourdon Over a year ago

Yeah, I've read this on another answer and I've read the review too. Thank you! @Daniel helped to get the result I wanted.

Collectives™ on Stack Overflow

Function returning table in WHERE clause, how to use result of query as parameter?

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related