0

I've hunted online and through documentation, but the much more common use case appears to be returning a SETOF some particular row type, not just a single row.

The use case below is trivial for illustration, as I am just trying to determine if this is even possible in plpgsql, and understand the syntax to use for it if it is. (Just like the Postgresql documentation contains examples of functions to return a static set of fixed values, the point here is not the use case but the syntax.)

With this table:

create table my_table (f1 int primary key, f2 text, f3 real, f4 text, f5 bool);

The following function can be created:

create function my_func1(f1_to_look_up int) returns my_table
language sql
as $$ select * from my_table where f1 = f1_to_look_up $$;

Also, this can be done in plpgsql like so, with explicit out parameters (and with f1 specified explicitly in the where clause to avoid ambiguity):

create function my_func2(f1_to_look_up int, out f1 int, out f2 text, out f3 real, out f4 text, out f5 bool)
language plpgsql
as $$
begin
  select * into strict f1, f2, f3, f4, f5
    from my_table where my_table.f1 = f1_to_look_up;
  return;
end
$$;

Is there a way to accomplish the same thing in plpgsql, without needing to repeat the definition of my_table in order to list all the OUT parameters?

  • What's wrong with returns setof my_table? – a_horse_with_no_name Dec 30 '19 at 20:49
  • @a_horse_with_no_name, maybe nothing. It seems odd to do that when the function will only ever return a single row. I wanted to understand the language syntax in this question, hence the trivial example. The actual context is a helper function to process a single record and return a single record, that will potentially be called by multiple wrapper functions that loop through records and use the helper function to return next by processing each in turn. – Wildcard Dec 30 '19 at 20:51
1

You can return a row type by using an expression with that type. here it's effectively select my_table from my_table

create function my_func4(f1_to_look_up int) returns my_table language plpgsql
as $$
    begin
        return my_table from my_table where f1 = f1_to_look_up;
    end; 
$$;

or via a variable

create or replace function my_func4(f1_to_look_up int) returns my_table language plpgsql
as $$
    declare
        retval my_table;
    begin
        select into retval * from my_table where f1 = f1_to_look_up;
        return retval;
    end; 
$$;

You can also use a row constructor, which would be especially useful if the query is more complicated and you need to return values taken from more than one table joined together:

create function my_func5(f1_to_look_up integer)
 returns my_table
 language plpgsql
as $function$
begin
  return row(t.*) from my_table as t where f1 = f1_to_look_up;
end
$function$
;

The key is that where the plpgsql syntax documentation indicates that you can write return expression, "expression" can be everything that would ordinarily follow "select" in a normal SQL query.

| improve this answer | |
  • Thanks! I've suggested an edit to add in the remaining information I was missing; I'll accept once that's approved. :) – Wildcard Dec 30 '19 at 18:31
0

I figured it out while writing the question; so far as I can tell this is the only way to do it:

create function my_func3(f1_to_look_up int, out my_result my_table)
language plpgsql
as $$
begin
  select * into strict my_result from my_table where f1 = f1_to_look_up;
  return;
end
$$;
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.