sqlpostgresqlset-returning-functions

how to send function result to temp table in PostgreSQL

I try to create a temporary table in PostgreSQL from the function result.

this is my funct :

CREATE OR REPLACE FUNCTION my_funct(var_code character)
    RETURNS TABLE(id bigint, name character)
    LANGUAGE 'plpgsql'
AS $BODY$
    BEGIN
    RETURN QUERY
    
    SELECT Id, Name FROM tbl WHERE Code=var_code;

END;
$BODY$;

I Have tried some script but it doesn't work

my script is below :

create temp table #tmpTable as select my_funct('xxx');

when I ran this script it was shown an error like this ERROR: column "my_funct" has pseudo-type record

Any ideas to achieve this? All thoughts will be appreciated

Solution

  • When a function returns a record, or set of records, instead of a single result, use the column names that you need or a * to get them all:

    create temp table #tmpTable as select * from my_funct('xxx');