Return type mismatch in function declared

Question

I am working in supabase, where I want to make a function with the help of SQL queries to obtain the last record of a user.

As it is a query that would not look entirely good using the traditional method using filters (Filters Supabase) I have decided to create a function that helps me with what I am trying to do, to then call the function with the .rpc method in my project (RPC FUNCTIONS)

So I took on the task (with the help of the community (How to select the last record of a table by user in PostgreSQL)) to first create a query to bring the information as I need it

select distinct on (t.id_user) t.*, u.username
   from "activity_historic_tracked" t
   inner join "user" u on u.id = t.id_user
   order by t.id_user, t.date desc;

Once I have the fully functional query, I took on the task of creating the function with the help of supabase

create or replace function testeo_user()
 returns setof activity_historic_tracked
 language plpgsql
 as
 $$
   begin
   return query
   select distinct on (t.id_user) t.*, u.username
   from "activity_historic_tracked" t
   inner join "users" u on u.id = t.id_user
   order by t.id_user, t.date desc;
   end;
$$

But the above returns the error

Failed to run sql query: return type mismatch in function declared to return activity_historic_tracked

According to what I was reading (Error trying to have a function return a string without first casting it as a string.) it could be for some kind of value but it is an answer that does not convince me because of the relationship I gave to the data. In another similiar question (PLPGSQL function(multiple column output)--gives error ) I noticed that all the fields of the tables were written directly without going through the "*" filter? is that really necessary? Isn't it enough to tell the function to return the table?

I made a demo in dbFiddle, I will leave it attached, I also wrote a script with a minimal example of how my database is structured, I hope I have been clear in the construction, I have a schema in the image but they told me that I should not upload images as examples when I ask questions.

  CREATE TABLE modality (id INTEGER PRIMARY KEY, name varchar(50));

CREATE TABLE rol (
  id    INTEGER PRIMARY KEY, 
  name  TEXT
);

CREATE TABLE jobPosition (
  id    INTEGER PRIMARY KEY, 
  name  varchar(50)
);

CREATE TABLE Member (
  id    INTEGER PRIMARY KEY, 
  timezone TEXT
);

CREATE TABLE users (
  id    INTEGER PRIMARY KEY, 
  name  varchar(50),
  username varchar(50),
  status varchar(50),
  dateOfEntry timestamp,
  leavingDate date,
  email varchar,
  id_modality INTEGER,
  id_rol INTEGER,
  id_jobPosition INTEGER,
  id_member INTEGER,
  FOREIGN KEY(id_modality) REFERENCES modality(id),
  FOREIGN KEY(id_rol) REFERENCES rol(id),
  FOREIGN KEY(id_jobPosition) REFERENCES jobPosition(id),
  FOREIGN KEY(id_member) REFERENCES Member(id)
);

CREATE TABLE activity_historic_tracked (
  id     INTEGER, 
  date   Date, 
  id_user INTEGER,
  tracked double precision,
  id_project INTEGER,
  id_rol INTEGER,
  id_modality INTEGER,
  FOREIGN KEY(id_user) REFERENCES users(id),
  FOREIGN KEY(id_modality) REFERENCES modality(id),
  FOREIGN KEY(id_rol) REFERENCES rol(id)
);

INSERT INTO modality( id, name ) VALUES (1, 'Freelancer');
INSERT INTO modality( id, name ) VALUES (2, 'Fulltime');
INSERT INTO modality( id, name ) VALUES (3, 'Parttime');

INSERT INTO rol( id, name ) VALUES (1, 'Developer');
INSERT INTO rol( id, name ) VALUES (2, 'Design');
INSERT INTO rol( id, name ) VALUES (3, 'Sac');

/* jobPosition */

INSERT INTO jobPosition( id, name ) VALUES (1, 'Junior');
INSERT INTO jobPosition( id, name ) VALUES (2, 'Senior');
INSERT INTO jobPosition( id, name ) VALUES (3, 'Trainee Fulltime');


/* Member */

INSERT INTO Member( id, timezone ) VALUES (1, 'Europe/Berlin');
INSERT INTO Member( id, timezone ) VALUES (2, 'America/Example');
INSERT INTO Member( id, timezone ) VALUES (3, 'America/Example3');

/* Users */

INSERT INTO users( id, name, username, status, dateOfEntry, leavingDate, email, id_modality, id_rol, id_jobPosition, id_member ) VALUES (1, 'Josh', 'User01', 'active', '2020-09-10', null, '[email protected]', 1, 1, 2, 3);
INSERT INTO users( id, name, username, status, dateOfEntry, leavingDate, email, id_modality, id_rol, id_jobPosition, id_member ) VALUES (2, 'Marsh', 'User02', 'active', '2019-09-10', null, '[email protected]', 2, 1, 2, 3);
INSERT INTO users( id, name, username, status, dateOfEntry, leavingDate, email, id_modality, id_rol, id_jobPosition, id_member ) VALUES (3, 'Frank', 'User03', 'active', '2018-09-10', null, '[email protected]', 2, 3, 2, 3);

/* Tracks */
INSERT INTO activity_historic_tracked(id, date, id_user, tracked, id_project, id_rol, id_modality) VALUES (1, CAST(N'2022-10-09' AS Date), 1, 20, 5364, 1, 1);
INSERT INTO activity_historic_tracked(id, date, id_user, tracked, id_project, id_rol, id_modality) VALUES (2, CAST(N'2022-10-10' AS Date), 2, 20, 5365, 1, 2);
INSERT INTO activity_historic_tracked(id, date, id_user, tracked, id_project, id_rol, id_modality) VALUES (3, CAST(N'2022-10-11' AS Date), 3, 5, 5366, 3, 2);
INSERT INTO activity_historic_tracked(id, date, id_user, tracked, id_project, id_rol, id_modality) VALUES (4, CAST(N'2022-11-08' AS Date), 1, 15, 5364, 1, 1);
INSERT INTO activity_historic_tracked(id, date, id_user, tracked, id_project, id_rol, id_modality) VALUES (5, CAST(N'2022-11-08' AS Date), 2, 20, 5365, 1, 2);
INSERT INTO activity_historic_tracked(id, date, id_user, tracked, id_project, id_rol, id_modality) VALUES (6, CAST(N'2022-11-04' AS Date), 3, 10, 5366, 3, 2);


SELECT * FROM modality;
SELECT * FROM rol;
SELECT * FROM jobPosition;
SELECT * FROM Member;
SELECT * FROM users;
SELECT * FROM activity_historic_tracked;

DEMO

minimal demonstration

I've been trying to do this for weeks, and I feel like I'm getting nowhere. I'm coming from MongoDB, I've been really trying, any kind of help will be appreciated.

Answer 1

Finally got to the solution of this problem. It turns out that when you want to return a table from the function, you use the RETURNS TABLE syntax and specify the columns of the table. In the function, we return a query that is the result of a SELECT statement and an INNER JOIN. When making the union of the two tables, I am returning not only the "activity-historic-tracked" table, I am also returning the "username" field of the "users" table, so it is necessary to specify each of these fields to our function that we hope to return.

And then what was my mistake? That it sent him a Set of "activity-historic-tracked" it sent him all the fields but at no time did he specify the fields that he was removing when doing the "inner join"

Another of the errors that resulted and that I think is important to mention (since I mentioned it in the question) is that if the data types of your fields do not match what you expect to return, you will also receive errors related to the one I was presenting.

The end result of my query was as follows:

create or replace function testeo_user2()
 returns table( id INTEGER
  , date date
  , id_user INTEGER
  , tracked double precision
  , id_project INTEGER
  , id_rol INTEGER
  , id_modality INTEGER
  , username character varying
)
 language plpgsql
 as
 $$
   begin
   return query
   select distinct on (t.id_user) t.id 
  , t.date 
  , t.id_user 
  , t.tracked  
  , t.id_project 
  , t.id_rol 
  , t.id_modality 
  , u.username 
   from "activity_historic_tracked" t
   inner join "users" u on u.id = t.id_user
   order by t.id_user, t.date desc;
   end;
$$

It was quite an odyssey, I had been trying for several days but I succeeded and I also understood a little more about SQL. Thank you very much for all the help that the community gives me.