Calling a function on every row returned by a subquery

Question

I need to run the following query to extract the values of my raster records in a specific point.

select st_value((select rast from mytable),
    (select st_GeomFromText('POINT(30.424 -1.978)', 4326)))

But I encounter with the following error:

ERROR: more than one row returned by a subquery used as an expression SQL state: 21000

It needs just one record for this function but I need to extract values of all of records.

Solution 1

If a subquery returns multiple rows, you must either use it in a common table expression (CTE / WITH query) and FROM alias, or use FROM (SELECT ...) alias. In this case, though, it looks like it's simpler than that:

select st_value(rast, st_GeomFromText('POINT(30.424 -1.978)', 4326))
FROM mytable;

Both subqueries appear to be unnecessary.

If you truly needed the subquery you'd write something syntactically like:

WITH sq(rast) AS ( SELECT rast FROM mytable )
SELECT st_value(rast, st_GeomFromText('POINT(30.424 -1.978)', 4326))
FROM sq;

or

SELECT st_value(rast, st_GeomFromText('POINT(30.424 -1.978)', 4326))
FROM (SELECT rast FROM mytable) sq(rast);

Solution 2

If you have a function with multiple columns, you can do something like this

SELECT (info).column1, (info).column2, (info).column3
FROM   (select st_value(rast, st_GeomFromText('POINT(30.424 -1.978)', 4326)) AS info
        FROM mytable
       ) AS foo