CODEWITHSUNDEEP
sqlpostgresqlfunction
Luffydude
Luffydude

Reputation: 772

Function to use as a query variable to return a result

Basically I need to create a function for a website guy to call a query search, he inputs an id and I need to return a number of ids from other tables that intersect with that id

My function is (doesnt work)

create or replace function return_id (id integer) 
 returns table (derpid integer, object text)
as $function$
begin
RETURN QUERY
select x.objectid, x.object_class 
from (
select objectid, object_class from table1 a where st_intersects(a.geom, (select geometry from searchtable where id = $1) ) and st_length(st_intersection(a.geom, (select geometry from ftth.cable where id = $1))) > 1 
) x ;
end;
$function$ language plpgsql VOLATILE
COST 100;

The idea is to have the $1 being the variable that the user wants to search for. I need to return the object id and object class but keep running into errors

ERROR: column reference "id" is ambiguous
Detail: It could refer to either a PL/pgSQL variable or a table column.

How to make this work?

Upvotes: 0

Views: 72

Answers (1)

user330315
user330315

Reputation:

The preferred way it to rename the parameter:

create or replace function return_id (p_id integer) 
  returns table (derpid integer, object text)
as $function$
  select x.objectid, x.object_class 
  from (
    select objectid, object_class 
    from table1 a 
    where st_intersects(a.geom, (select geometry from searchtable where id = p_id) ) 
      and st_length(st_intersection(a.geom, (select geometry from ftth.cable where id = p_id))) > 1 
  ) x ;
$function$ 
language sql
stable;

Another way is to qualify the column reference (e.g. by using a table alias):

create or replace function return_id (id integer) 
  returns table (derpid integer, object text)
as $function$
  select x.objectid, x.object_class 
  from (
    select objectid, object_class 
    from table1 a 
    where st_intersects(a.geom, (select geometry from searchtable st where st.id = $1) ) 
      and st_length(st_intersection(a.geom, (select geometry from ftth.cable c where c.id = $1))) > 1 
  ) x ;
$function$ 
language sql
stable;

Note that I also changed the function to language sql which is typically more efficient for simple functions wrapping a query.

Upvotes: 1

Related Questions