0

I write a SQL function that receives and return a record. I need to use the received record in a from clause. I am using unnest(array[the_variable]).

Is there a specific function or structure to do this? (something like record_to_recordset(the_variable))

Here is my example:

create type calculate_info_type as (super_code text, super_name text);

create function calculate_info(p_country country) 
   returns calculate_info_type 
   language SQL as
$SQL$
   SELECT super_code, super_name 
     FROM unnest(array[p_country]), -- <=== HERE!!
          lateral (select 
            prefix is not null as has_prefix
          ) g1,valores que tenga la tabla
          lateral (select 
            case has_prefix when true then iso||'-'||prefix else iso end as super_code,
            case has_prefix when true then name else name||' without phone prefix' end as super_name
          ) g2 
$SQL$;

I put a complete running example here: https://rextester.com/XIRSW33377

  • So you want the columns of that record variable as rows? I am not sure I understand what you are after, could you please Edit your question and add some sample data and the expected output based on that data? – a_horse_with_no_name Jan 9 at 21:30
  • No I do not. I want the same behavior of unnest (array [row_var]). I need to transform a record into a recordset with one record. I need to put row_var in the from clausule – Emilio Platzer Jan 9 at 21:40
  • Not in this case because I put my var inside an array [ x ] construct. Unnest returns a recordset with one row (as I expect) because the array has one element (of type record). But I feel someway unhappy to do it in this way. – Emilio Platzer Jan 9 at 22:05
1

Short answer (edited)

You can write FROM (select p_country.*) some_alias instead of FROM unnest(array[p_country])

Question code with minimal corrections: https://rextester.com/UGKJR74935

Original answer (with many usefull suggestions)

Looks like you want something like this:

create function calculate_info(p_country country) 
   returns calculate_info_type 
   language SQL as
$SQL$
   select case when has_prefix then iso||'-'||prefix else iso end as super_code,
          case when has_prefix then name else name||' without phone prefix' end as super_name
   FROM (
      select (p_country).*, p_country.prefix is not null as has_prefix
   ) as c
$SQL$;

But you don't really need the from at all:

create function calculate_info(p_country country) 
   returns calculate_info_type 
   language SQL 
as
$SQL$
   select concat_ws(',', p_country.iso, p_country.prefix) super_code,
          case 
            when p_country.prefix is not null then p_country.name 
            else p_country.name||' without phone prefix' 
          end as super_name;
$SQL$;

concat_ws will return only the value from the iso field if prefix is null.

You don't need a lateral to use that function:

select iso, prefix, name, (calculate_info(country)).*
from country

Your updated example: https://rextester.com/GVLK52206

  • Are there known problems for using lateral? I use lateral because I write many variables based in previous calculated variables (my example was simplificated). When I write in the traditional way I need to nest many level of selects. With lateral I can write the same thing without nesting a select for each group of variables. – Emilio Platzer Jan 9 at 22:48

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.