Hi everyone! I am trying to create a function however I am having some trouble getting it to return anything. When I call

select fetch_data()

it says "structure of query does not match function result type"

create or replace function fetch_data()
  returns setof "SolanaFloorTracker"
  language plpgsql
  as $$
    begin
    return query
      SELECT rank_filter.* 
      FROM (
        SELECT "SolanaFloorTracker".*, 
        rank() OVER (
            PARTITION BY "CollectionName"
            ORDER BY created_at DESC
        )
        FROM "SolanaFloorTracker"
      ) rank_filter WHERE RANK <=2;
    end;
  $$

I also tried

returns setof record

but the output is set-valued function called in context that cannot accept a set. I tested the inner SQL query and it works, so I think I just need to learn about what I need to return to get this to work properly?

Sorry I was chekcing my code before posing.

sql
CREATE TABLE IF NOT EXISTS "SolanaFloorTracker"
(
    id  INT,
    "CollectionName" TEXT,
    CREATED_AT TIMESTAMP
);

INSERT INTO "SolanaFloorTracker"(id, "CollectionName", Created_at)
VALUES
  (1,'A', '2010-01-19')
, (3,'B', '2010-09-18')
, (4,'A', '2021-05-15')
, (4,'A', '2021-07-25');

  -- drop  FUNCTION f_foo();
CREATE OR REPLACE FUNCTION f_foo() -- (open_id numeric) -- parameter not used
  RETURNS TABLE (id  INT,
    "CollectionName" TEXT,
    CREATED_AT TIMESTAMP, rank bigint) AS
$func$
BEGIN
RETURN QUERY SELECT rank_filter.id, rank_filter."CollectionName", rank_filter.Created_at, rank_filter.rank
      FROM (
        SELECT sft.*, 
        rank() OVER (
            PARTITION BY sft."CollectionName"
            ORDER BY sft.created_at DESC
        ) as rank
        FROM "SolanaFloorTracker" sft
      ) rank_filter WHERE rank_filter.rank <=2; 
END
$func$  LANGUAGE plpgsql;
    

    select * from f_foo()

Am I doing something incorrect here? I think I altered it a bit to match what you had

create or replace function fetch_data()
  returns table ( id INT, "CollectionName" TEXT, created_at timestamp, "FloorPrice" json, "Volume" json, rank bigint)
  as $func$
    begin
    return query
      SELECT rank_filter.id, rank_filter.created_at, rank_filter."CollectionName", rank_filter."FloorPrice", rank_filter."Volume", rank_filter.rank
      FROM (
        SELECT sft.*, 
        rank() OVER (
            PARTITION BY sft."CollectionName"
            ORDER BY sft.created_at DESC
        ) as rank
        FROM "SolanaFloorTracker" sft
      ) rank_filter WHERE rank_filter.RANK <=2;
    end;
  $func$ language plpgsql

However, when I run

select * from fetch_data()

it returns "structure of query does not match function result type"

Everything will have to match exactly as far as types. I notice your order is different also in your select versus your table and that might be an issue as table may just assume order and not actually look at column names for a match, based on my limited looking at earlier. Also to debug you might cut back your columns to bare minimum then add them to find the issue, if the basic one worked. It is a pain though as you have to likely drop the function each time you change table.

I'll see if making the returned table order match the SELECT order

are you using timestamptz with zone?

It was that but I edited the column to be without in the normal table...

your id is also bigint or int8, not sure what is needed

I swapped created_at column back to timestamptz and made that match in the returned table, and then in the returned table I changed ID into to int8 and it does work!

I learned alot too with rank and table. Hopefully I need it in my project at some point 😉