Postgres stored function return result of SELECT DISTINCT-CodePudding

I want to have an array of distinct integer values across my postgres table as the return value of a stored function.

The stored function currently looks like this

create or replace function get_unique_entries(id int)
   returns table ("entry_id" int)
language plpgsql
as
$$
begin
   return query  
      select distinct table.entry_id 
      from my_table 
      where x = id;
end;
$$;

When executing select get_unique_entries(2);, I get the following error message:

structure of query does not match function result type

I tried different return types, but nothing worked for me.

Thanks in advance!

CodePudding user response：

Hmm, can you give us a more complete picture of your scenario? I tried using your code and it seems to work (except I needed to replace table with my_table):

postgres=# create table my_table(x int, entry_id int, name text);
CREATE TABLE
postgres=# insert into my_table values(generate_series(1,100),generate_series(1,10),'foo');
INSERT 0 100
postgres=# create or replace function get_unique_entries(id int)
postgres-#    returns table ("entry_id" int)
postgres-# language plpgsql
postgres-# as
postgres-# $$
postgres$# begin
postgres$#    return query  
postgres$#       select distinct table.entry_id 
postgres$#       from my_table 
postgres$#       where x = id;
postgres$# end;
postgres$# $$;
ERROR:  syntax error at or near "table"
LINE 8:       select distinct table.entry_id 
                              ^
postgres=# create or replace function get_unique_entries(id int)
   returns table ("entry_id" int)
language plpgsql
as
$$
begin
   return query  
      select distinct my_table.entry_id 
      from my_table 
      where x = id;
end;
$$;
CREATE FUNCTION
postgres=# select get_unique_entries(2);
 get_unique_entries 
--------------------
                  2
(1 row)

postgres=#

CodePudding user response：

While preparing the complete example I actually found it out myself.

As I am working with supabase, they display the datatype BIGINT as int8. I was trying to set this as return type. Setting the return type to BIGINT instead worked.

So in general check I would recommend myself and to others to check your column data types exactly.

The working example looks like this (as indicated by @richyen)

create or replace function get_unique_categories_for_platform(platformId int)
returns table ("category_fk" bigint)
   language plpgsql
as
$$
begin
    return query select distinct course.category_fk
    from course
    where platform_fk = platformId;
end;
$$;