andrew andrew - 6 days ago 7
SQL Question

pgadmin, sql, array function

I can't make function with array data.

First I made a function like this




create or replace function get_source(ageom geometry)
returns integer as
$$
declare
location geometry;
snote integer;
begin
location := st_transform(ageom,5179);
select source into snote
from pedroad a
order by st_distance(st_transform(a.geom,5179),location);
return snote;
end
$$
language plpgsql;





I run this function
like




select get_source((select geom from location));


In this case location have only one row
enter image description here

Then I can get one result.

But now I want get more than one result
If I have more than one row like
enter image description here

I want to get two results for search row.

I want to know exactly more than one row , two row, tree row, four row any case, that function can be move..

Answer

You need to call it as part of a regular select statement:

select get_source(geom), name, place
from location;

But that will be very inefficient as you are running the select inside the function for each and every row in the location table.

Comments