Setting return table data type be the same type as another table-CodePudding

I have a postgres table function currently declared as such:

CREATE OR REPLACE FUNCTION apolloqa.my_func(arguments...) 
 RETURNS TABLE(first_name text, last_name text, age int)   
LANGUAGE plpgsql AS $function$
 BEGIN     
RETURN QUERY         
    select first_name, last_name, age         
    from person_table         ;
END 
$function$ ;

When I run this code, postgres complains that the first_name, and last_name in the return table does not match the query's return type. That is true. But how do I declare first_name and last_name so that it either matches the query's return type or the underlying person_table's column type without repeating the same type? Is there a way to say something like:

RETURNS TABLE(first_name TYPE is person_table.first_name, ... ) ?

Postgres has a 'like' feature, but it selects all columns from a given table. I want to select just a few from one table, and a few from another. My solutions in the past would be to hard code the datatype from the underlying table, so varchar(150), or something. But, I'd like to have the type reference another type, if that's possible.

CodePudding user response：

You can use Copying type

By using %TYPE you don't need to know the data type of the structure you are referencing, and most importantly, if the data type of the referenced item changes in the future (for instance: you change the type of user_id from integer to real), you might not need to change your function definition.

https://www.postgresql.org/docs/current/plpgsql-declarations.html#PLPGSQL-DECLARATION-TYPE

your function INPUT and OUTPUT parameter name the same with query body column name. That may lead to some mistake in the future. See DB fiddle: the last code block

CREATE OR REPLACE FUNCTION apolloqa.my_func(p_age person_table.age%type)
 RETURNS TABLE(_first_name person_table.first_name%type,
                _last_name person_table.last_name%type,
                 _age  person_table.age%type)
LANGUAGE plpgsql AS $function$
BEGIN
RETURN QUERY 
    select first_name, last_name, age from person_table where age = p_age;
END
$function$;

CodePudding user response：

Yes, you can do almost exactly you indicated, the syntax is just a little different. Use

 returns table(first_name  person_table.first_name%type 
              ,last_name   person_table.last_name%type
              ,age int
              );

Since your function has just SQL you can also define it as an SQL function:

create or replace function  my_func(arguments text) 
 returns table(first_name  person_table.first_name%type 
              ,last_name   person_table.last_name%type
              ,age int
              )
language  sql as $function$          
    select first_name, last_name, age         
    from person_table ;
$function$ ;