"Lambda" function returns type string instead of type record

Question

I am using PostgreSQL 9.5.4 and trying to create a lambda function based on this code: https://github.com/decibel/pg_lambda/blob/master/sql/lambda.sql

set client_min_messages to debug;

create or replace function lambda_record(
    code text,
    variadic inputs anyarray
) returns record language plpgsql as $code$
declare
    fname text;
    foid regprocedure;
    fargs regtype[];
    call_args text[];
    sql text;
    out record;
begin
    -- name of function
    fname := format('pg_temp.lambda_function_%s', md5(code));
    raise debug 'fname=%', fname;

    -- create function
    sql := format('create function %s%s', fname, code);
    raise debug 'sql=%', sql;
    execute sql;

    -- get function id and arguments
    foid := fname::regproc::oid;
    select proargtypes::regtype[] into fargs
        from pg_proc where oid = foid;

    call_args := array(
        select format('%L::%s', input, argtype)
            from unnest(inputs, fargs) as u(input, argtype)
    );
    sql := format(
        'select %s(%s)',
        foid::oid::regproc,
        array_to_string(call_args, ', ')
    );
    raise debug 'sql=%', sql;

    -- out is a string but should be type record
    execute sql into out using inputs;
    raise debug 'out=%', out;

    -- drop function
    sql := format('drop function %s', foid::text);
    raise debug 'sql=%', sql;
    execute sql;

    return out;
end;
$code$;

select lambda_record(
$lambda$(
    IN arg1 numeric,
    IN arg2 numeric,
    OUT out1 numeric,
    OUT out2 numeric
) returns record language sql AS $$
    select $1::numeric, $2::numeric;
$$
$lambda$, 1, 2);

The problem is that the out variable in lambda_record() returns a string instead of a record:

SET
Time: 0.539 ms
psql:/Users/ian/Desktop/lambda3.sql:55: LOG:  duration: 2.759 ms
CREATE FUNCTION
Time: 3.445 ms
psql:/Users/ian/Desktop/lambda3.sql:64: DEBUG:  fname=pg_temp.lambda_function_f1ee9780eb612939b06081fce4ba88cb
psql:/Users/ian/Desktop/lambda3.sql:64: DEBUG:  sql=create function pg_temp.lambda_function_f1ee9780eb612939b06081fce4ba88cb(
        IN arg1 numeric,
        IN arg2 numeric
    ) returns record language sql AS $$
        select $1::numeric, $2::numeric;
    $$

psql:/Users/ian/Desktop/lambda3.sql:64: DEBUG:  sql=select pg_temp_2.lambda_function_f1ee9780eb612939b06081fce4ba88cb('1'::numeric, '2'::numeric)
psql:/Users/ian/Desktop/lambda3.sql:64: DEBUG:  out=("(1,2)")
psql:/Users/ian/Desktop/lambda3.sql:64: DEBUG:  sql=drop function pg_temp_2.lambda_function_f1ee9780eb612939b06081fce4ba88cb(numeric,numeric)
psql:/Users/ian/Desktop/lambda3.sql:64: LOG:  duration: 7.511 ms
┌───────────────┐
│ lambda_record │
├───────────────┤
│ ("(1,2)")     │
└───────────────┘
(1 row)

Time: 7.625 ms
psql:/Users/ian/Desktop/lambda3.sql:69: LOG:  duration: 0.407 ms

For some reason the line execute sql into out using inputs sets out to a RECORD which contains a single value which is a TEXT representation of what it should be.

But it should work like this:

create or replace function pg_temp.test(in arg1 integer, in arg2 integer,
    out out1 integer, out out2 integer)
language sql as $$
    select $1, $2;
$$;
select pg_temp.test(1, 2);
CREATE FUNCTION
Time: 0.529 ms
psql:/Users/ian/Desktop/lambda3.sql:70: LOG:  duration: 0.193 ms
┌───────┐
│ test  │
├───────┤
│ (1,2) │
└───────┘
(1 row)

I've been using Postgres for a long time, but I'm relatively new to the PL/pgsql language so any would be very appreciated.

Answer 1

As lambda_record function returns RECORD and doesn't have OUT parameter, you must define the columns and types it will return when calling using an alias:

SELECT r.out1, r.out2
FROM lambda_record(...) AS r(out1 numeric, out2 numeric);

In this case r is the table alias, and out1/out2 the alias for the columns with the types defined (defining types is only necessary when it returns RECORD and doesn't have OUT parameters).

EDIT: even though it uses a RECORD, lambda_record still calls the function as it had only one column returned, using SELECT func_name(params...) method:

sql := format(
    'select %s(%s)',
    foid::oid::regproc,
    array_to_string(call_args, ', ')
);
raise debug 'sql=%', sql;

You could change that line to call it use SELECT * FROM func_name(params...):

sql := format(
    'select * from %s(%s)',
    foid::oid::regproc,
    array_to_string(call_args, ', ')
);
raise debug 'sql=%', sql;

That way you should already have it return the columns correctly (even for functions with a single column), and now you can call as I showed in the beginning.

Answer 2

The reason is that your EXECUTE is returning a single column. I did a code like this one and it worked:

SELECT array_to_string(inputs,',') INTO columns;
EXECUTE 'SELECT ' || columns INTO out;

I couldn't dig more to fix the integer/numeric issue (you will see that I'm using integer,integer for the columns reference, but at least we know that the problem is how EXECUTE is being handled.

so=# select * from lambda_record(
$lambda$(
IN arg1 numeric,
IN arg2 numeric,
OUT out1 numeric,
OUT out2 numeric
) returns record language sql AS $$
select $1::numeric, $2::numeric;
$$
$lambda$, 1, 2) as (i integer, b integer);
 i | b 
---+---
 1 | 2
(1 row)

so=# select lambda_record(
$lambda$(
IN arg1 numeric,
IN arg2 numeric,
OUT out1 numeric,
OUT out2 numeric
) returns record language sql AS $$
select $1::numeric, $2::numeric;
$$
$lambda$, 1, 2);
 lambda_record 
---------------
 (1,2)
(1 row)