Calling a Postgres function from a sql statement gives error

Question

I am trying to call a function in Postgres, from within my sql statement. It gives me a "column xxx not found" error. My SQL code is below.

SELECT r.resource_id, r.name, owner.name, r.status,
r.cost, r.display_unit, r.next_available_date, 
geodistance(r.latitude, r.longitude, 41.824, 71.4128) as distance 
FROM resource_item as r 
INNER JOIN base_user AS owner 
ON r.resource_owner = owner.username 
WHERE distance < 100 
ORDER BY distance, r.next_avail_date, r.name;

I get the following error:

ProgrammingError: column "distance" does not exist

The function geodistance() itself has been tested as an individual statement like the one below and works fine.

select geodistance(38.898556, -77.037852, 38.897147, -77.043934) as distance from incident;

I am able to get a response if I put the geodistance() function in the WHERE clause, but I don't know how to do the ORDER BY.

I am stuck a bit. I need to evaluate the distance and be able to compare with an input value. How can I achieve this?

Pavel Stehule · Accepted Answer · 2016-11-08 06:29:23Z

1

You cannot use label as column name in WHERE clause in same level query.

CREATE OR REPLACE FUNCTION public.one()
 RETURNS integer
 LANGUAGE sql
AS $function$ select 1 $function$

postgres=# select one() as fooa from generate_series(1,3) where one < 10;
ERROR:  column "one" does not exist 
LINE 1: ...lect one() as fooa from generate_series(1,3) where one < 10;

You have to use subquery

postgres=# select * from (select one() as fooa from generate_series(1,3)) s where fooa < 10;  
+------+
| fooa |
+------+
|    1 |
|    1 |
|    1 |
+------+
(3 rows)

answered Nov 8, 2016 at 6:29

Pavel Stehule

46.6k6 gold badges103 silver badges102 bronze badges

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

1 Comment

Ramesh Ramasubramanian Over a year ago

I appreciate your quick response. This worked like a charm. Thank you.

Collectives™ on Stack Overflow

Calling a Postgres function from a sql statement gives error

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related