Обсуждение: missing support of named convention for procedures

Hi

create or replace procedure proc2(in a int, in b int)
as $$
begin
  a := a * 10;
  b := b * 10;
end;
$$ language plpgsql;

postgres=# call proc2(a => 10,b => 20);
ERROR:  XX000: unrecognized node type: 107
LOCATION:  ExecInitExprRec, execExpr.c:2114

Regards

Pavel

2018-03-15 22:13 GMT+01:00 Pavel Stehule <pavel.stehule@gmail.com>:

Hi

create or replace procedure proc2(in a int, in b int)
as $$
begin
  a := a * 10;
  b := b * 10;
end;
$$ language plpgsql;

postgres=# call proc2(a => 10,b => 20);
ERROR:  XX000: unrecognized node type: 107
LOCATION:  ExecInitExprRec, execExpr.c:2114

Defaults are not supported too:

 postgres=# create or replace procedure foo1(a int, b int, c int default 10)
as $$
begin
  raise notice 'a: %, b: %, c: %', a, b, c;
end;
$$ language plpgsql;
CREATE PROCEDURE
postgres=# call foo1(10,20);
NOTICE:  00000: a: 10, b: 20, c: -778600432
LOCATION:  exec_stmt_raise, pl_exec.c:3643
CALL

Regards

Pavel

2018-03-16 8:43 GMT+01:00 Pavel Stehule <pavel.stehule@gmail.com>:

2018-03-15 22:13 GMT+01:00 Pavel Stehule <pavel.stehule@gmail.com>:

Hi

create or replace procedure proc2(in a int, in b int)
as $$
begin
  a := a * 10;
  b := b * 10;
end;
$$ language plpgsql;

postgres=# call proc2(a => 10,b => 20);
ERROR:  XX000: unrecognized node type: 107
LOCATION:  ExecInitExprRec, execExpr.c:2114

Defaults are not supported too:

 postgres=# create or replace procedure foo1(a int, b int, c int default 10)
as $$
begin
  raise notice 'a: %, b: %, c: %', a, b, c;
end;
$$ language plpgsql;
CREATE PROCEDURE
postgres=# call foo1(10,20);
NOTICE:  00000: a: 10, b: 20, c: -778600432
LOCATION:  exec_stmt_raise, pl_exec.c:3643
CALL

attached patch fixes it

Regards

Pavel

 

Regards

Pavel

On 3/16/18 06:29, Pavel Stehule wrote:
> attached patch fixes it

The fix doesn't seem to work for LANGUAGE SQL procedures.  For example:

CREATE PROCEDURE ptest5(a int, b int DEFAULT 0)
LANGUAGE SQL
AS $$
INSERT INTO cp_test VALUES (a, 'foo');
INSERT INTO cp_test VALUES (b, 'bar');
$$;
CALL ptest5(a => 1, b => 2);  -- ok
CALL ptest5(b => 3, a => 4);  -- ok
CALL ptest5(5);
ERROR:  no value found for parameter 2
CONTEXT:  SQL function "ptest5" statement 2
CALL ptest5(a => 6);
ERROR:  no value found for parameter 2
CONTEXT:  SQL function "ptest5" statement 2

I'm not quite sure why this behaves differently from plpgsql.  Needs
more digging.

--
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On 3/22/18 10:19, Pavel Stehule wrote:
> attached patch should to fix it

Committed.

I had to make a small tweak to make INOUT + DEFAULT parameters work in
PL/pgSQL.

--
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services