andrew andrew - 1 year ago 104
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
location geometry;
snote integer;
location := st_transform(ageom,5179);
select source into snote
from pedroad a
order by st_distance(st_transform(a.geom,5179),location);
return snote;
language plpgsql;

I run this function

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 Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download