1

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.

2 Answers 2

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.

3
  • That still doesn't work. out is a string before lamda_record() returns: psql:/Users/ian/Desktop/lambda3.sql:64: DEBUG: out=("(1,2)") Aug 28, 2016 at 17:28
  • 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. Aug 28, 2016 at 17:33
  • I see the problem... Updating with (what I consider) a better code for lambda_record...
    – MatheusOl
    Aug 29, 2016 at 12:39
0

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.