Question 114404 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Postgres stored function return result of SELECT DISTINCT

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!

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    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;
$$;

  2. 0

    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=#
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search