PostgreSQL call function returning setof record with table and additional columns

Question

I am trying to call a function returning all columns of an existing table + some unrelated additional ones and retrieve the returned data using the following syntax:

select *
from test_func(...)
as (a_table my_table_name, rows_count numeric);

The function has the following format:

CREATE OR REPLACE FUNCTION public.test_func(...)
 RETURNS SETOF record
 LANGUAGE plpgsql
AS $function$
    
DECLARE
    _sql VARCHAR;
begin
    
    _sql := 'SELECT mtn.*, count(*) over() as rows_count  
        from public.my_table_name mtn
        ... inner joins and other stuff';

    return query
    execute _sql using ..._sql params...;

END;
$function$
;

However, it doesn't work. The error I receive:

ERROR: structure of query does not match function result type
  Detail: Returned type numeric does not match expected type "my_table_name" in column 1.

Instead of SELECT mtn.*, use the actual column name that contains the table name — Shameel
– Shameel, Commented Aug 27, 2021 at 9:49
@Shameel I don't understand. How do I select all the table columns then? — Mihai Catalin Valentin
– Mihai Catalin Valentin, Commented Aug 27, 2021 at 9:54

Laurenz Albe · Accepted Answer · 2021-08-27 09:55:17Z

1

If the query in your function should return a my_table_name (a “whole-row reference), you should write it like

SELECT mtn, count(*) OVER () ...

answered Aug 27, 2021 at 9:55

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL call function returning setof record with table and additional columns

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