\pset null _null_
\pset format unaligned
SET client_min_messages = warning;
SET ROLE postgres;
CREATE TABLE customers(cust_id bigint NOT NULL,cust_name varchar(32) NOT NULL,cust_address text,
cust_country text) PARTITION BY LIST(cust_country);
CREATE TABLE customer_ind PARTITION OF customers FOR VALUES IN ('ind');
CREATE TABLE customer_jap PARTITION OF customers FOR VALUES IN ('jap');
CREATE TABLE customer_def PARTITION OF customers DEFAULT;
INSERT INTO customers VALUES (2039,'Puja','Hyderabad','ind');
SELECT tableoid::regclass,* FROM customers;
tableoid|cust_id|cust_name|cust_address|cust_country
customer_ind|2039|Puja|Hyderabad|ind
(1 row)
SELECT * FROM customer_ind;
cust_id|cust_name|cust_address|cust_country
2039|Puja|Hyderabad|ind
(1 row)
UPDATE customers SET cust_country ='jap' WHERE cust_id=2039;
SELECT * FROM customer_jap;
cust_id|cust_name|cust_address|cust_country
2039|Puja|Hyderabad|jap
(1 row)
select ddlx_script('customers');
ddlx_script
BEGIN;

/*
DROP TABLE customers;
*/

-- Type: TABLE ; Name: customers; Owner: postgres

CREATE TABLE customers (
    cust_id bigint NOT NULL,
    cust_name character varying(32) NOT NULL,
    cust_address text,
    cust_country text
)
  PARTITION BY LIST (cust_country);

COMMENT ON TABLE customers IS NULL;
ALTER TABLE customers OWNER TO postgres;


END;

(1 row)
select ddlx_script('customer_jap');
ddlx_script
BEGIN;

/*
DROP TABLE customer_jap;
*/

-- Type: TABLE ; Name: customer_jap; Owner: postgres

CREATE TABLE customer_jap PARTITION OF customers
  FOR VALUES IN ('jap');

COMMENT ON TABLE customer_jap IS NULL;
ALTER TABLE customer_jap OWNER TO postgres;


END;

(1 row)
select ddlx_script('customer_def');
ddlx_script
BEGIN;

/*
DROP TABLE customer_def;
*/

-- Type: TABLE ; Name: customer_def; Owner: postgres

CREATE TABLE customer_def PARTITION OF customers
  DEFAULT;

COMMENT ON TABLE customer_def IS NULL;
ALTER TABLE customer_def OWNER TO postgres;


END;

(1 row)
-- statistics
CREATE TABLE test_stat (
    a   int primary key,
    b   int
);
CREATE STATISTICS test_stat1 (dependencies) ON a, b FROM test_stat;
select ddlx_create(oid) from pg_statistic_ext where stxname='test_stat1';
ddlx_create
CREATE STATISTICS public.test_stat1 (dependencies) ON a, b FROM test_stat;
ALTER STATISTICS test_stat1 OWNER TO postgres;

(1 row)
select ddlx_script('test_stat');
ddlx_script
BEGIN;

/*
DROP TABLE test_stat;
*/

-- Type: TABLE ; Name: test_stat; Owner: postgres

CREATE TABLE test_stat (
    a integer NOT NULL,
    b integer
);

COMMENT ON TABLE test_stat IS NULL;
ALTER TABLE test_stat OWNER TO postgres;

ALTER TABLE test_stat ADD CONSTRAINT test_stat_pkey
  PRIMARY KEY (a);

CREATE STATISTICS public.test_stat1 (dependencies) ON a, b FROM test_stat;
ALTER STATISTICS test_stat1 OWNER TO postgres;


END;

(1 row)
-- test hash partitioning
create table dept (id  int primary key) partition by hash(id) ;
create table dept_1 partition of dept FOR VALUES WITH (MODULUS 10, REMAINDER 0);
create table dept_2 partition of dept FOR VALUES WITH (MODULUS 10, REMAINDER 1);
create table dept_3 partition of dept FOR VALUES WITH (MODULUS 10, REMAINDER 2);
create table dept_4 partition of dept FOR VALUES WITH (MODULUS 10, REMAINDER 3);
create table dept_5 partition of dept FOR VALUES WITH (MODULUS 10, REMAINDER 4);
create table dept_6 partition of dept FOR VALUES WITH (MODULUS 10, REMAINDER 5);
create table dept_7 partition of dept FOR VALUES WITH (MODULUS 10, REMAINDER 6);
create table dept_8 partition of dept FOR VALUES WITH (MODULUS 10, REMAINDER 7);
create table dept_9 partition of dept FOR VALUES WITH (MODULUS 10, REMAINDER 8);
create table dept_10 partition of dept FOR VALUES WITH (MODULUS 10, REMAINDER 9);
select ddlx_script('dept');
ddlx_script
BEGIN;

/*
DROP TABLE dept;
*/

-- Type: TABLE ; Name: dept; Owner: postgres

CREATE TABLE dept (
    id integer NOT NULL
)
  PARTITION BY HASH (id);

COMMENT ON TABLE dept IS NULL;
ALTER TABLE dept OWNER TO postgres;

ALTER TABLE dept ADD CONSTRAINT dept_pkey
  PRIMARY KEY (id);


END;

(1 row)
select ddlx_script('dept_7');
ddlx_script
BEGIN;

/*
DROP TABLE dept_7;
*/

-- Type: TABLE ; Name: dept_7; Owner: postgres

CREATE TABLE dept_7 PARTITION OF dept
  FOR VALUES WITH (modulus 10, remainder 6);

COMMENT ON TABLE dept_7 IS NULL;
ALTER TABLE dept_7 OWNER TO postgres;

ALTER TABLE dept_7 ADD CONSTRAINT dept_7_pkey
  PRIMARY KEY (id);

ALTER TABLE dept_7 ADD CONSTRAINT dept_7_pkey PRIMARY KEY (id);


END;

(1 row)
-- test procedures
CREATE PROCEDURE procedure1(IN p1 TEXT)
AS $$
BEGIN
    RAISE WARNING 'Procedure Parameter: %', p1 ;
END ;
$$
LANGUAGE plpgsql ;
call procedure1('Hello, world!');
WARNING:  Procedure Parameter: Hello, world!
select ddlx_script('procedure1(text)');
ddlx_script
BEGIN;

/*
DROP PROCEDURE procedure1(text);
*/

-- Type: PROCEDURE ; Name: procedure1(text); Owner: postgres

CREATE OR REPLACE PROCEDURE public.procedure1(p1 text)
 LANGUAGE plpgsql
AS $procedure$
BEGIN
    RAISE WARNING 'Procedure Parameter: %', p1 ;
END ;
$procedure$;

COMMENT ON PROCEDURE procedure1(text) IS NULL;

ALTER PROCEDURE procedure1(text) OWNER TO postgres;
REVOKE ALL ON FUNCTION procedure1(text) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION procedure1(text) TO PUBLIC;
GRANT EXECUTE ON FUNCTION procedure1(text) TO postgres WITH GRANT OPTION;

END;

(1 row)