Function to use as a query variable to return a result

Question

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?

Just to be sure, you should name your parameter p_id or so, it's a match for id, too... — Turo
– Turo, Commented Mar 24, 2021 at 19:49

user330315 · Accepted Answer · 2021-03-24 20:11:10Z

1

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.

answered Mar 24, 2021 at 20:11

user330315

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

4 Comments

Luffydude Over a year ago

It worked thanks! Though is there a way to output the result as a table? I'm getting the output (just, like, this) in one column

user330315 Over a year ago

You need to use select * from return_id(...)

Luffydude Over a year ago

you mean returns table (derpid integer, object text) as $function$ select * from ( x.objectid, x.object_class from ( .... ) x ) y ?

user330315 Over a year ago

No. When you use the function, use it like a table. select * from ...

Collectives™ on Stack Overflow

Function to use as a query variable to return a result

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related