Generating PostgreSQL Types With CodeJohnny

We're going to demonstrate creating PostgreSQL Types with CodeJohnny. I blogged about using CodeJohnny to create PGSQL in Generating PostgreSQL Functions with CodeJohnny not long ago if you'd like to compare and contrast.

PostgreSQL Types are used in a number of ways including being returned from PostgreSQL functions. Here is an example of a PostgreSQL function declaration that returns a PostgreSQL Type object set.

I'm returning Types from functions all of the time so I decided to create a shell of a PostgreSQL function in the CodeJohnny Template as well. We'll see that in the generated PGSQL at the end of the post along with the template we're using.

Generating the Fields in CodeJohnny

The PGSQL to create PostgreSQL Types is very similar to creating a PostgreSQL Table.

We'll be creating the fields in our Type using a Java Method defined in our CodeJohnny Template's <methods /> list. The PostgreSQL function we're generating includes the creation of a temporary table, and we'll be able to use the same Java Method for both. As usual when generating repeating patterns in CodeJohnny, we'll be looping through a CodeJohnnyColumn object list of our source table and populate a pattern based on the column's data type and properties.

Here's the pgFieldLoop() Java method we'll be using for the creation of the PostgreSQL Type and the temporary table fields. We're using two base patterns to generate the field definition, one with the field name and data type, and the second that includes the data length. We can include more DataTypes in our Switch loop but will include only those in this demonstration.

Here is the CodeJohnny Template which defines the pgFieldLoop method and the fieldLoop tag we'll be populating in the method.

The generated PGSQL code can now be run as a PostgreSQL Query.

--
-- Name: type_nixmashup_link; Type: TYPE; Schema: public; Owner: postgres
--

CREATE TYPE type_nixmashup_link AS (
link_id INT4,
nixmashup_id INT4,
link_title VARCHAR(255),
link_url VARCHAR(255),
link_domain VARCHAR(60),
link_text VARCHAR(2000),
link_html VARCHAR(2000),
link_datetime TIMESTAMP(6),
link_clicks INT4,
link_tags VARCHAR(500)
);


ALTER TYPE public.type_nixmashup_link OWNER TO postgres;


------------------------------------------------


--
-- Name: p_nixmashup_links_get(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION p_nixmashup_links_get() RETURNS SETOF type_nixmashup_links
    LANGUAGE plpgsql
    AS $$BEGIN

CREATE TEMP TABLE tmp_nixmashup_links (
link_id INT4,
nixmashup_id INT4,
link_title VARCHAR(255),
link_url VARCHAR(255),
link_domain VARCHAR(60),
link_text VARCHAR(2000),
link_html VARCHAR(2000),
link_datetime TIMESTAMP(6),
link_clicks INT4,
link_tags VARCHAR(500)
);


INSERT INTO tmp_nixmashup_links SELECT
*, ''
FROM nixmashup_links;

RETURN QUERY select * from tmp_nixmashup_links order by "link_id";

drop table tmp_nixmashup_links;

END
$$;

That's a lot of typing we don't have to do! Below is the complete CodeJohnny template we're using which we can reuse again and again in the future by simply changing “nixmashup_links” in the template datatable and properties value.

<?xml version="1.0" encoding="UTF-8"?>
<template xmlns:ns2='com.nixmash.codejohnny'>
	<name>PostgreSql PLPSGSQL Type and Procedure Creation Template</name>
	<description>
		Creates PostgreSql Type and shell of Procedure which returns SetOf(Type)
	</description>
	<dataclass>NixMashupLink</dataclass>
	<datatable>z_nixmashup_links</datatable>
	<connection>postgresql.nixmashupdb</connection>
	<properties>
		<property name="baseTypeName" value="nixmashup_link" type="java.lang.String"
			category="Context" description="Type and. ex: 'type_[nixmashup_link]'"
			defaultvalue="CodeJohnny" />
		<property name="baseTableName" value="nixmashup_links" type="java.lang.String"
			category="Context" description="Type and. ex: 'tmp_[nixmashup_links]'"
			defaultvalue="CodeJohnny" />
	</properties>
	<methods>
		<method name="fieldLoop" method="pgFieldLoop" 
			type="com.codejohnny.client.CodeJohnnyMethods" 
			pattern="link_title varchar(255)," />
	</methods>
	<content><![CDATA[
	
--
-- Name: type_{baseTypeName}; Type: TYPE; Schema: public; Owner: postgres
--

CREATE TYPE type_{baseTypeName} AS (
{{fieldLoop}}
);


ALTER TYPE public.type_{baseTypeName} OWNER TO postgres;


------------------------------------------------


--
-- Name: p_{baseTableName}_get(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION p_{baseTableName}_get() RETURNS SETOF type_{baseTableName}
    LANGUAGE plpgsql
    AS $$BEGIN

CREATE TEMP TABLE tmp_{baseTableName} (
{{fieldLoop}}
);


INSERT INTO tmp_{baseTableName} SELECT
*, ''
FROM {baseTableName};

RETURN QUERY select * from tmp_{baseTableName} order by "{primarykey}";

drop table tmp_{baseTableName};

END
$$;

	
	]]>
	</content>
</template>

Visit codejohnny.com if you want to learn more about generating Java and SQL code with CodeJohnny.

Posted May 26, 2014 12:16 PM EDT

More Like This Post