CODEWITHSUNDEEP
postgresqlplpgsqlpostgresql-9.3
Amar Anugu
Amar Anugu

Reputation: 27

Executing multiple select statements with different data types using functions

How can I execute multiple select statements with different data types using functions in postgresql?

CREATE OR REPLACE FUNCTION multiple3()
  RETURNS TABLE(a text, b text, c character varying, d character varying, e character varying, f character varying, g character varying,h character varying) AS
$BODY$
BEGIN

RETURN QUERY select a,b,c from table 1;

RETURN QUERY select e,f,g,h from table 2;

END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;


-- here a and e are different data types

Upvotes: 2

Views: 3204

Answers (1)

klin
klin

Reputation: 121834

You cannot return rows with different data types from plpgsql function in the sense that all returned values in one call must be of the same type.

If you want to return rows with variable number of columns you should declare record or setof record as return type of the function. In this case however you have to specify expected row structure in the function call.

Functions returning one row should have record as returned type. Example:

create or replace function test_record(number_of_columns int)
returns record language plpgsql as $$
begin
    case number_of_columns
        when 1 then 
            return row('a'::text);
        when 2 then 
            return row('a'::text, 2::integer);
        else 
            return row('a'::text, 2::integer, 5.5::numeric);
    end case;
end $$;

When executing a query you have to know how many columns the function returns:

select * from test_record(1) as (column1 text);

 column1 
---------
 a

select * from test_record(2) as (column1 text, column2 integer);

 column1 | column2 
---------+---------
 a       |       2

select * from test_record(3) as (column1 text, column2 integer, column3 numeric);

 column1 | column2 | column3 
---------+---------+---------
 a       |       2 |     5.5

-- but:

select * from test_record(3) as (column1 text);
ERROR:  returned record type does not match expected record type
DETAIL:  Number of returned columns (3) does not match expected column count (1).
CONTEXT:  PL/pgSQL function test_record(integer) while casting return value to function's return type

Functions returning more than one row should have setof record as returned type: 

create or replace function test_set_of_record(number_of_columns int)
returns setof record language plpgsql as $$
begin
    case number_of_columns
        when 1 then 
            return query select i::text from generate_series(1, 3) i;
        when 2 then 
            return query select i::text, i from generate_series(1, 3) i;
        else 
            return query select i::text, i, i::numeric from generate_series(1, 3) i;
    end case;
end $$;

select * from test_set_of_record(3) as (column1 text, column2 integer, column3 numeric);

 column1 | column2 | column3 
---------+---------+---------
 1       |       1 |       1
 2       |       2 |       2
 3       |       3 |       3
(3 rows)

Read more: 7.2.1.4. Table Functions.

Another option is to use null values to fit a row to returned type, example:

create or replace function test_with_nulls()
returns table (col1 text, col2 int, col3 numeric)
language plpgsql as $$
begin
    return query select 'one'::text, null::int, null::numeric;
    return query select 'two'::text, 2::int, null::numeric;
    return query select 'three'::text, 3::int, 3.3::numeric;
end $$;

select * from test_with_nulls();

 col1  | col2 | col3 
-------+------+------
 one   |      |     
 two   |    2 |     
 three |    3 |  3.3
(3 rows)

Upvotes: 1

Related Questions