Thread: Re: postgresql referencing and creating types as record

create or replace package CUM_A_TYPES
as
 type LT_PAYMENT is record
(BASIC    number,
  DP    number,
  GRADE_PAY                number
);
TYPE TYPE_CALC_TAX is record
 (
  FIN_ROLE_ID   number(8),
  CALC_FOR_ROLE_CODE  number(4));

NEED TO CONVERT THIS TO POSTGRES , ANYBODY PLS HELP ME I M NEW TO POSTGRES
THX&RGDS
VPMM



--
View this message in context:
http://postgresql.1045698.n5.nabble.com/postgresql-referencing-and-creating-types-as-record-tp5813901p5814171.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On 08/07/2014 09:50 PM, vpmm2007 wrote:
> create or replace package CUM_A_TYPES
> as
>   type LT_PAYMENT is record
> (BASIC    number,
>    DP    number,
>    GRADE_PAY                number
> );
> TYPE TYPE_CALC_TAX is record
>   (
>    FIN_ROLE_ID   number(8),
>    CALC_FOR_ROLE_CODE  number(4));
>
> NEED TO CONVERT THIS TO POSTGRES , ANYBODY PLS HELP ME I M NEW TO POSTGRES


There is no CREATE PACKAGE in the community version of Postgres.

The pay version of EnterpriseDB does have it:


http://www.enterprisedb.com/docs/en/9.3/oracompat/Postgres_Plus_Advanced_Server_Oracle_Compatibility_Guide-30.htm#P2593_148612

For an idea of the other Oracle features it has, see here:

http://www.enterprisedb.com/docs/en/9.3/oracompat/Table%20of%20Contents.htm#TopOfPage

If you do not want to use the above, then you will have to go another
route. What that is, will depend on what you are trying to achieve. From
a cursory look at packages, they are a defined grouping of objects
stored as such in the database. The nearest solution I can come up at
the moment is to store the object creation statements in an external
script and run that against the database. This does leave you in charge
of handling changes to the objects. So some sort of version control
would be helpful. Some examples:

Sqitch

http://sqitch.org/

Alembic

http://alembic.readthedocs.org/en/latest/


> THX&RGDS
> VPMM


--
Adrian Klaver
adrian.klaver@aklaver.com

On Thu, Aug 7, 2014 at 11:50 PM, vpmm2007 <vaishalim2007@gmail.com> wrote:
> create or replace package CUM_A_TYPES
> as
>  type LT_PAYMENT is record
> (BASIC    number,
>   DP    number,
>   GRADE_PAY                number
> );
> TYPE TYPE_CALC_TAX is record
>  (
>   FIN_ROLE_ID   number(8),
>   CALC_FOR_ROLE_CODE  number(4));

Looks like something like:

CREATE TYPE LT_PAYMENT AS
(
  BASIC numeric,
  DP numeric,
  GRADE_PAY numeric
);

CREATE TYPE TYPE_CALC_TAX AS
(
  FIN_ROLE_ID numeric(8),
  CALC_FOR_ROLE_CODE numeric(8)
);

CREATE OR REPLACE FUNCTION some_function() RETURNS LT_PAYMENT  AS
$$
...
$$ LANGAUGE PLPGSQL;

I'd be studying the pl/pgsql documentation and the data type
differences (for number, you'd want to use int, numeric, or float8
depending on circumstances).

merlin