0

I'm very, very new to writing Postgres functions. I'm writing a function to build a materialized path for a child by searching for parents recursively starting with the child's id. Here's the function I have, but I keep getting the error ERROR: RETURN cannot have a parameter in function returning set

create or replace function build_mp(child_id text)
returns SETOF text 
language plpgsql
as $$
begin
    select parentid from account where childid = child_id;
    if parentid IS NULL then
        return ARRAY[child_id];
    else
        return build_mp(parentid) || ARRAY[child_id];
    end if;
end $$;

SELECT build_mp('mychild');

What am I doing wrong?

Edit

Here's the working solution. It takes a child's id, then searches recursively for all parents above it building a material path for the new child item.

create or replace function build_mp(child_id text)
returns text[] 
language plpgsql
as $$
declare
    parent text;
begin
    execute 'select parentid from account where childid = $1' INTO parent USING child_id;
    if parent IS NULL THEN
        return ARRAY[child_id];
    else
        return build_mp(parent) || ARRAY[child_id];
    end if;
end $$;

SELECT build_mp('mychild') AS mp;
Improve this question
0

2 Answers 2

Reset to default
3

To overcome the "ERROR: query has no destination for result data" error you don't need dynamic SQL.

You can select into a variable directly:

select parentid into parent from account where childid = child_id;

But you can simplify your function by using a recursive CTE and a SQL function. That will perform a lot better especially with a large number of levels:

create or replace function build_mp(child_id text)
  returns text[]
language sql
as 
$$
   with recursive all_levels (childid, parentid, level) as (
      select childid, parentid, 1
      from account
      where childid = child_id
      union all
      select c.childid, c.parentid, p.level + 1
      from account c
       join all_levels p on p.parentid = c.childid
   )
   select array_agg(childid order by level)
   from all_levels;
$$;
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Thanks. I'll take a look at the recursive CTE. Wasn't aware of it.
Your function gives the following error: return type mismatch in function declared to return text[]
@LoneWolfPR: should work if childid is defined as text
Ah, childid is actually varchar, so had to change that line to select array_agg(text(childid) order by level)...
@LoneWolfPR: you should change the return type varchar[] instead.
2

If you want to return an array of text, you have to declare the function as

... RETURNS text[]

instead of

... RETURNS SETOF text

A set returning function returns a table, while an array is a single value of type text[]. In a set returning function, you would use RETURN NEXT child_id for every row you return and RETURN (without an argument) to terminate function processing.

PostgreSQL complains that you use RETURN value within a set returning function, which is not allowed.

Improve this answer

2 Comments

That fixed the function. Thanks! Now however, I'm getting the error from my select at the end that says Error: ERROR: query has no destination for result data. What is the problem there?
Nevermind. I figured out it. I'm updating the question to show the final working solution. Thanks!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.