return type mismatch in function declared to return posts-CodePudding

hello I have a query that passes to a psql function, to this I will pass 2 arguments lat and lng, the problem is that it should return an array of objects [{id:..,distance:..},..] I am using setof on return, should setof posts return to me?

CREATE TABLE posts (
  id bigint generated by default as identity primary key,
  user_id uuid references auth.users not null,
  user_email text,
  title text,
  content text,
  latitude float,
  longitude float,
  created_at timestamp with time zone default timezone('utc'::text, now()) not null,
  updated_at timestamp with time zone default timezone('utc'::text, now()) not null
);

CREATE FUNCTION near_places( lat float, lng float ) RETURNS SETOF posts AS $$

SELECT
    id,
    distance

FROM 
    (
        SELECT
            id,
            (
                3959 *
                acos(
                    cos( radians($1) ) *
                    cos( radians( latitude ) ) * 
                    cos( radians( longitude ) - radians($2) )  
                    sin( radians(-32.63) ) * 
                    sin( radians( latitude ) )
                )
            ) AS distance
        FROM
            posts
    ) p 

WHERE
    distance < 25

ORDER BY
    distance LIMIT 20

$$ language sql;

CodePudding user response：

Your near_places function returns 2 columns: id, distance, but your posts table has a completely different set of columns - so your function does not actually return a SETOF posts - it returns a custom table-type, so change your DDL to RETURNS TABLE() which describes the id, distance columns:

CREATE FUNCTION near_places( lat float, lng float )
RETURNS TABLE(
    id       int,
    distance real /* or decimal, numeric, etc */
) AS $$

SELECT
    id,
    distance

FROM
    /* etc */

$$ language sql;