PLPGSQL distinct on, order by, returns nothing

Question

I made this plpgsql function and it doest return me anything! while if i take out the query part and execute this in a seperate sql window it returns the correct rows.

I also think the query isnt really optimal so any help is appreciated (very new to plpgsql)

CREATE OR REPLACE FUNCTION get_members(in_company_uuid uuid, in_start integer, in_limit integer, in_sort character varying, in_order character varying, OUT out_status integer, OUT out_status_description character varying, OUT out_value character varying[]) RETURNS SETOF record
LANGUAGE plpgsql
AS $$DECLARE

temp_record RECORD;
temp_out_value VARCHAR[];
temp_iterator INTEGER := 0;

BEGIN

FOR temp_record IN EXECUTE '
SELECT DISTINCT ON
(' || in_sort || ')
u.user_uuid,
u.firstname,
u.preposition,
u.lastname,
array(SELECT email FROM emails WHERE user_uuid = u.user_uuid) as emails,
array(SELECT mobilenumber FROM mobilenumbers WHERE user_uuid = u.user_uuid) as mobilenumbers,
array(SELECT c.name FROM targetgroupusers AS tgu LEFT JOIN membercategories as mc ON mc.targetgroup_uuid = tgu.targetgroup_uuid LEFT JOIN categories AS c ON mc.category_uuid = c.category_uuid WHERE tgu.user_uuid = u.user_uuid) as categories,
array(SELECT color FROM membercategories WHERE targetgroup_uuid IN(SELECT targetgroup_uuid FROM targetgroupusers WHERE user_uuid = u.user_uuid)) as colors
FROM
    membercategories AS mc
LEFT JOIN
    targetgroups AS tg
ON
    tg.targetgroup_uuid = mc.targetgroup_uuid
LEFT JOIN
    targetgroupusers AS tgu
ON
    tgu.targetgroup_uuid = tg.targetgroup_uuid
LEFT JOIN
    users AS u
ON
    u.user_uuid = tgu.user_uuid
WHERE
    mc.company_uuid = \'' || in_company_uuid || '\'
ORDER BY
   ' || in_sort || ' ' || in_order || '
OFFSET
    ' || in_start || '
LIMIT
    ' || in_limit

LOOP
  temp_out_value[temp_iterator] = ARRAY[temp_record.user_uuid::VARCHAR(36), temp_record.firstname::CHARACTER VARYING, temp_record.preposition::CHARACTER VARYING, temp_record.lastname::CHARACTER VARYING, temp_record.emails::CHARACTER VARYING, temp_record.mobilenumbers::CHARACTER VARYING, temp_record.categories::CHARACTER VARYING, temp_record.colors::CHARACTER VARYING];
  temp_iterator = temp_iterator+1;
END LOOP;

out_status := 0;
out_status_description := 'Members retrieved';
out_value := temp_out_value;
RETURN;

END$$;

Thanks a lot!

Answer 1

You need to use RETURN NEXT temp_record instead of "just" RETURN in order to return a complete result set.

This is the part of the manual that explains how to handle a "RETURNS SETOF" function:

In that case, the individual items to return are specified by a sequence of RETURN NEXT or RETURN QUERY commands, and then a final RETURN command with no argument is used to indicate that the function has finished executing

Answer 2

Don't use \ to escape, use quotes:

\'' || in_company_uuid || '\'

''' || in_company_uuid || '''

But, I wouldn't use function for this, just a plain SQL query. Use EXPLAIN to see how it's executed and where the performance problems can be found. It ain't a very nice piece of SQL...

Ps. Your function is also vulnerable to SQL injection, you put userinput without any escaping into the SQL.

Answer 3

You need to use

CREATE OR REPLACE FUNCTION get_members(...) RETURNS record LANGUAGE plpgsql AS
...

ie remove the setof which is your problem as a_horse mentions

But bear in mind that your getting only 1 row like this. You can unnest that array if you want multiple rows by following this example:

create or replace function unnest(anyarray) returns setof anyelement as $$
  select $1[i] from generate_series(array_lower($1,1), array_upper($1,1)) i;
$$ language'sql' immutable;

create function func(out text, out text[]) returns record language plpgsql as $$
begin
  $1='success';
  $2[1]='hello';
  $2[2]='there';
  $2[3]='Koen';
  return;
end;$$;

select * from unnest((select column2 from func()));

 unnest
--------
 hello
 there
 Koen

If you are on 8.4 or above then there is no need to create your own unnest function as I've done here.