DROP TABLE IF EXISTS X;
CREATE TABLE X (
  a integer
);

INSERT INTO X (a)
  VALUES (1), (2), (3), (4), (5), (6), (7);

DROP TABLE IF EXISTS Y;
CREATE TABLE Y (col1 INT, col2 INT);
INSERT INTO Y (col1,col2)
  VALUES (1,2), (2,0), (0,4), (4,0), (0,6), (6,7), (7,8);

DROP TABLE IF EXISTS Z;
CREATE TABLE Z (col1 BYTEA);

DROP TABLE IF EXISTS deposits;
CREATE TABLE deposits(
       deposit_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY
      ,shard INT8 NOT NULL
      ,coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)
      ,known_coin_id INT8 NOT NULL
      ,amount_with_fee_val INT8 NOT NULL
      ,amount_with_fee_frac INT4 NOT NULL
      ,wallet_timestamp INT8 NOT NULL
      ,exchange_timestamp INT8 NOT NULL
      ,refund_deadline INT8 NOT NULL
      ,wire_deadline INT8 NOT NULL
      ,merchant_pub BYTEA NOT NULL CHECK (LENGTH(merchant_pub)=32)
      ,h_contract_terms BYTEA NOT NULL CHECK (LENGTH(h_contract_terms)=64)
      ,coin_sig BYTEA NOT NULL CHECK (LENGTH(coin_sig)=64)
      ,wire_salt BYTEA NOT NULL CHECK (LENGTH(wire_salt)=16)
      ,wire_target_h_payto BYTEA CHECK (LENGTH(wire_target_h_payto)=32)
      ,done BOOLEAN NOT NULL DEFAULT FALSE
      ,policy_blocked BOOLEAN NOT NULL DEFAULT FALSE
      ,policy_details_serial_id INT8);


DROP FUNCTION IF EXISTS pg_spi_insert_int;
CREATE FUNCTION pg_spi_insert_int()
  RETURNS VOID
  LANGUAGE c VOLATILE COST 100
AS '$libdir/own_test', 'pg_spi_insert_int';

DROP FUNCTION IF EXISTS pg_spi_select_from_x;
CREATE FUNCTION pg_spi_select_from_x()
  RETURNS INT8
  LANGUAGE c COST 100
AS '$libdir/own_test', 'pg_spi_select_from_x';

/*
CREATE FUNCTION pg_spi_select_pair_from_y()
  RETURNS valuest
  LANGUAGE c COST 100
AS '$libdir/own_test', 'pg_spi_select_pair_from_y';
*/
/*CREATE FUNCTION pg_spi_select_with_cond()
  RETURNS INT8
  LANGUAGE c COST 100
AS '$libdir/own_test', 'pg_spi_select_with_cond';
*/

DROP FUNCTION IF EXISTS pg_spi_update_y;
CREATE FUNCTION pg_spi_update_y()
  RETURNS VOID
  LANGUAGE c VOLATILE COST 100
AS '$libdir/own_test', 'pg_spi_update_y';

DROP FUNCTION IF EXISTS pg_spi_prepare_example;
CREATE FUNCTION pg_spi_prepare_example()
  RETURNS INT8
  LANGUAGE c COST 100
AS '$libdir/own_test', 'pg_spi_prepare_example';

DROP FUNCTION IF EXISTS pg_spi_prepare_example_without_saveplan;
CREATE FUNCTION pg_spi_prepare_example_without_saveplan()
  RETURNS INT8
  LANGUAGE c COST 100
AS '$libdir/own_test', 'pg_spi_prepare_example_without_saveplan';

DROP FUNCTION IF EXISTS pg_spi_prepare_insert;
CREATE FUNCTION pg_spi_prepare_insert()
  RETURNS VOID
  LANGUAGE c VOLATILE COST 100
AS '$libdir/own_test', 'pg_spi_prepare_insert';

DROP FUNCTION IF EXISTS pg_spi_prepare_insert_without_saveplan;
CREATE FUNCTION pg_spi_prepare_insert_without_saveplan()
  RETURNS VOID
  LANGUAGE c VOLATILE COST 100
AS '$libdir/own_test', 'pg_spi_prepare_insert_without_saveplan';

/*
CREATE FUNCTION pg_spi_prepare_select_with_cond()
  RETURNS INT8
  LANGUAGE c COST 100
AS '$libdir/own_test', 'pg_spi_prepare_select_with_cond';
*/

DROP FUNCTION IF EXISTS pg_spi_prepare_select_with_cond_without_saveplan;
CREATE FUNCTION pg_spi_prepare_select_with_cond_without_saveplan()
  RETURNS INT8
  LANGUAGE c COST 100
AS '$libdir/own_test', 'pg_spi_prepare_select_with_cond_without_saveplan';

DROP FUNCTION IF EXISTS pg_spi_prepare_update;
CREATE FUNCTION pg_spi_prepare_update()
  RETURNS VOID
  LANGUAGE c VOLATILE COST 100
AS '$libdir/own_test', 'pg_spi_prepare_update';

DROP FUNCTION IF EXISTS pg_spi_get_dep_ref_fees;
CREATE FUNCTION pg_spi_get_dep_ref_fees(
  IN in_timestamp INT8
 ,IN merchant_pub BYTEA
 ,IN wire_target_h_payto BYTEA
 ,IN wtid BYTEA
)
  RETURNS VOID
  LANGUAGE c VOLATILE COST 100
AS '$libdir/own_test', 'pg_spi_get_dep_ref_fees';

DROP FUNCTION IF EXISTS update_pg_spi_get_dep_ref_fees;
CREATE FUNCTION update_pg_spi_get_dep_ref_fees(
 IN in_refund_deadline INT8,
 IN in_merchant_pub BYTEA,
 IN in_wire_target_h_payto BYTEA
)
RETURNS SETOF record
LANGUAGE plpgsql VOLATILE
AS $$
DECLARE

BEGIN
RETURN QUERY
  UPDATE deposits
  SET done = TRUE
  WHERE NOT (done OR policy_blocked)
  AND refund_deadline < in_refund_deadline
  AND merchant_pub = in_merchant_pub
  AND wire_target_h_payto = in_wire_target_h_payto
  RETURNING
  deposit_serial_id,
  coin_pub,
  amount_with_fee_val,
  amount_with_fee_frac;
END $$;

DROP FUNCTION IF EXISTS stored_procedure_update;
CREATE FUNCTION stored_procedure_update(
IN in_number INT8
)
RETURNS VOID
LANGUAGE plpgsql
AS $$
BEGIN
  UPDATE Y
  SET col1 = 4
  WHERE col2 = in_number;
END $$;

DROP FUNCTION IF EXISTS stored_procedure_select;
CREATE FUNCTION stored_procedure_select(OUT out_value INT8)
RETURNS INT8
LANGUAGE plpgsql
AS $$
BEGIN
  SELECT 1
    INTO out_value
  FROM X;
  RETURN;
END $$;


DROP FUNCTION IF EXISTS stored_procedure_insert;
CREATE FUNCTION stored_procedure_insert(
IN in_number INT8,
OUT out_number INT8)
RETURNS INT8
LANGUAGE plpgsql
AS $$
BEGIN
  INSERT INTO X (a)
  VALUES (in_number)
  RETURNING a INTO out_number;
END $$;

DROP FUNCTION IF EXISTS stored_procedure_select_with_cond;
CREATE FUNCTION stored_procedure_select_with_cond(
IN in_number INT8,
OUT out_number INT8
)
RETURNS INT8
LANGUAGE plpgsql
AS $$
BEGIN
 SELECT col1 INTO out_number
 FROM Y
 WHERE col2 = in_number;
 RETURN;
END $$;