Postgres function to return Table into variables

Question

How can I capture different columns into different variables like so (note this is only pseudocode so I am assuming it will cause errors. Example taken from here)

create or replace function get_film (
  p_pattern varchar
) 
    returns table (
        film_title varchar,
        film_release_year int
    ) 
    language plpgsql
as $$
begin
    return query 
        select
            title,
            release_year::integer
        from
            film
        where
            title ilike p_pattern;
end;$$

create or replace function get_film_into_variables (
  p_pattern varchar
)
returns null
    language plpgsql
as $$
declare
    v_title varchar,
    v_release_year integer
begin
    SELECT
        get_film (p_pattern)
    INTO
        v_title,
        v_release_year;

end;$$

Belayer · Accepted Answer · 2020-10-02 06:23:02Z

Assuming you have some purpose for the variables after retrieving them not just ending the function your "get_film_into_variables" is almost there. But first let's backup just a bit. A function that returns a table does just that, you can use the results just like a table stored on disk (it just goes away after query or calling function ends). To that end only a slight change to the "get_film_into_variables" function is required. The "get_film" becomes the object of the FROM clause. Also change the returns null, to returns void. So

create or replace function get_film_into_variables (
  p_pattern varchar
)
returns void
    language plpgsql
as $$
declare
    v_title varchar;
    v_release_year integer;

begin
    select *
      from get_film (p_pattern)
    INTO
        v_title,
        v_release_year;
end;
$$;

The above works for a single row returned by a function returning table. However for a return of multiple rows you process the results of the table returning function just lake you would an actual table - with a cursor.

create or replace 
function get_film_into_variables2(p_pattern varchar)
  returns void
  language plpgsql
as $$
declare
    k_message_template constant text = 'The film "%s" was released in %s.';
    v_title varchar;
    v_release_year integer;
    v_film_message varchar; 
   
    c_film  cursor (c_pattern varchar) for
        select * from  get_film (c_pattern);
      
begin    
    open c_film (p_pattern);
    loop
        fetch c_film
         into v_title 
            , v_release_year;  
        exit when not found;

        v_film_message = format( k_message_template,v_title,v_release_year::text);
        raise notice using
           message = v_film_message;
      
    end loop;
end;
$$;

BTW: the get_film function can be turned into a SQL function. See fiddle here. For demo purposes get_film_into_variable routines return a message.

Collectives™ on Stack Overflow

Postgres function to return Table into variables

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related