18.3. Calling PostgreSQL Functions or Procedures

• URL:cf

• POST data:{"n":"function name","fp":"function or procedure","p":[[parameter mode,parameter type#,parameter value]]}

  Table 18-1. Function or Procedure

  fp	function or procedure
  function	f
  procedure	p

  Table 18-2. Parameter Mode

  p	Parameter Mode	Comment
  IN	i	Value for the parameter of this mode is mandatory.
  INOUT	b	Value for the parameter of this mode is mandatory.
  OUT	o	Constant value NULL must be provided to the parameter of this mode.

  Table 18-3. parameter type#

  parameter type	parameter type#
  company code	491
  user account	38
  language code	87
  characters	79
  number	457
  boolean	467

• returns:(values returned by called PostgreSQL function)

example 1: Calling PostgreSQL function close_accounts

• request:

  curl -v -H "Content-Type:Application/json" -H 'Authorization:abHEi47obYMiCi193xjEiWIFpOouNmkd' -H 'X-Auth-Database:dev5' -d '{"n":"close_accounts","fp":"f","p":[["i",491,"1"],["i",457,2019],["i",457,12],["i",79,"en"]]}' https://34.terarows.com/fastcgi/api/cf

  This request can be imagined as sending in this SQL command to be performed on database dev5: SELECT * FROM close_accounts('1',2019,12,'en')

• response:

  "@362 \"8\", @364 \"9\""

example 2: Calling PostgreSQL function my_func

• Assume database dev5 has the following table and procedure:

  CREATE TABLE t( c1 INTEGER ); CREATE FUNCTION my_func(OUT a INTEGER,b INTEGER) AS $$ INSERT INTO t VALUES (b) RETURNING b+1 $$ LANGUAGE SQL;

  request

  curl -v -H "Content-Type:Application/json" -H 'Authorization:abHEi47obYMiCi193xjEiWIFpOouNmkd' -H 'X-Auth-Database:dev5' -d '{"n":"my_func","fp":"f","p":[["i",457,5]]}' https://34.terarows.com/fastcgi/api/cf

  This request can be imagined as sending in this SQL command to be performed on database dev5: SELECT * FROM my_proc(5)

• response:

  "@362 \"8\", @364 \"6\""

example 3: Calling PostgreSQL procedure my_proc

• Assume database dev5 has the following table and procedure:

  CREATE TABLE t( c1 INTEGER ); CREATE PROCEDURE my_proc(OUT a INTEGER,b INTEGER) AS $$ INSERT INTO t VALUES (b) RETURNING b+1 $$ LANGUAGE SQL;

  request

  curl -v -H "Content-Type:Application/json" -H 'Authorization:abHEi47obYMiCi193xjEiWIFpOouNmkd' -H 'X-Auth-Database:dev5' -d '{"n":"my_proc","fp":"p","p":[["o",NULL,NULL],["i",457,5]]}' https://34.terarows.com/fastcgi/api/cf

  This request can be imagined as sending in this SQL command to be performed on database dev5: CALL my_proc(NULL,5)

• response:

  "@362 \"8\", @364 \"6\""