postgresql referencing and creating types as record

vpmm2007 wrote

type function is record (f1 NUMERIC,f2 NUMERIC..); this is in oracle

kindly tell me what is the substitute to use "is record " in postgres.

its urgent .....

thanks and rgds
vpmm

No idea on exactly what Oracle is creating here (a type or a set returning
function) but either:

CREATE TYPE ( ... )

or

CREATE FUNCTION ( ... ) RETURNS TABLE ( ... )

The documentation will provide specifics.

http://www.postgresql.org/docs/9.3/interactive/sql-commands.html

David J.

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

In reply to this post by vpmm2007
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-tp5813901p5814176.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 08/07/2014 10:23 PM, vpmm2007 wrote:

In reply to this post by vpmm2007
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

Well you have not provided context for where and how you are using this,
so I will wing it.

From here:

http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/record_definition.htm

I suspect it is being used in a pl/sql function. In that case Davids
previous pots holds. Look in:

CREATE TYPE
http://www.postgresql.org/docs/9.3/interactive/sql-createtype.html

CREATE FUNCTION
http://www.postgresql.org/docs/9.3/interactive/sql-createfunction.html

pl/pgsql
http://www.postgresql.org/docs/9.3/interactive/plpgsql.html

In particular:

http://www.postgresql.org/docs/9.3/interactive/plpgsql-declarations.html

If you want a more specific answer, we will need more specific information.

THX&RGDS
VPMM

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

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 08/10/2014 09:50 PM, Vaishali Maheshkar wrote:

SIR,

THANKS FOR YOUR REPLY ,

CCing list.

actually i m trying to create record type data in postgres , but i cant
declare it directly as in oracle my purpose is to create types of record
type in oracle it was done by creating a package and declaring all the
types inside it.
Then used it with
other packages as :
create or replace function IT_R_A_get_se_CE_without_org
(
in_LT_PAYMENT in CUM_A_TYPES. LT_PAYMENT
out_se_ce_hashmap out
IT_CUSTOM_RECORD_TYPES.LT_NUMBER_HASHMAP
)
as..

i want to convert this package to postgres.

it would be great if i get the solution.

The solution(s) have already been presented. Either get EDB Advanced
Server Plus and its Oracle compatibility features or use the links below
to craft your own solution. The basic issue is the community version of
Postgres does not have packages, so you will need to go another route if
you stick with it. That route would most likely revolve around using
CREATE TYPE to create the types you had in the package. Another way
would be to use IN and OUT parameters to a function which is shown in
the links below. The method you choose is dependent on the bigger
picture of what you are doing with the database/application/middleware.
Only you have that full picture, so there is only so much we can do from
this end.

thx a lot

On Fri, Aug 8, 2014 at 7:15 PM, Adrian Klaver <adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.com>> wrote:

On 08/07/2014 10:23 PM, vpmm2007 wrote:

In reply to this post by vpmm2007
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

Well you have not provided context for where and how you are using
this, so I will wing it.

From here:

http://docs.oracle.com/cd/__B19306_01/appdev.102/b14261/__record_definition.htm
<http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/record_definition.htm&gt;

I suspect it is being used in a pl/sql function. In that case Davids
previous pots holds. Look in:

CREATE TYPE
http://www.postgresql.org/__docs/9.3/interactive/sql-__createtype.html
<http://www.postgresql.org/docs/9.3/interactive/sql-createtype.html&gt;

CREATE FUNCTION
http://www.postgresql.org/__docs/9.3/interactive/sql-__createfunction.html
<http://www.postgresql.org/docs/9.3/interactive/sql-createfunction.html&gt;

pl/pgsql
http://www.postgresql.org/__docs/9.3/interactive/plpgsql.__html
<http://www.postgresql.org/docs/9.3/interactive/plpgsql.html&gt;

In particular:

http://www.postgresql.org/__docs/9.3/interactive/plpgsql-__declarations.html
<http://www.postgresql.org/docs/9.3/interactive/plpgsql-declarations.html&gt;

If you want a more specific answer, we will need more specific
information.

THX&RGDS
VPMM

--
View this message in context:
http://postgresql.1045698.n5.__nabble.com/postgresql-__referencing-and-creating-__types-as-record-__tp5813901p5814176.html
<http://postgresql.1045698.n5.nabble.com/postgresql-referencing-and-creating-types-as-record-tp5813901p5814176.html&gt;
Sent from the PostgreSQL - general mailing list archive at
Nabble.com.

--
Adrian Klaver
adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>

--
Regards
Vaishali Maheshkar

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general