Postgres stored function return result of SELECT DISTINCT

Question

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!

If you want "an array of distinct integers" then you should return an array, not a "table" — user330315
– user330315, Commented Sep 29, 2022 at 17:28
Your description says you want array of distinct integer values, however your function returns returns a table of integers. these are not even closely the same thing. Thus the structure difference between what your app (un-posted) expects and what Postgres returns. Further you need to update your question with your table definition (ddl) and sample data with expected results. All as test - no images. — Belayer
– Belayer, Commented Sep 29, 2022 at 17:34

richyen · Accepted Answer · 2022-09-29 18:23:34Z

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=#

Thomas K. · Accepted Answer · 2022-10-01 14:32:49Z

0

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;
$$;

answered Oct 1, 2022 at 14:32

Thomas K.

1392 silver badges11 bronze badges

Collectives™ on Stack Overflow

Postgres stored function return result of SELECT DISTINCT

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related