Re: [GENERAL] postgresql referencing and creating types as record
On Thu, Aug 7, 2014 at 11:50 PM, vpmm2007 vaishalim2...@gmail.com wrote: create or replace package CUM_A_TYPES as type LT_PAYMENT is record (BASICnumber, DPnumber, GRADE_PAYnumber ); 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
Re: [GENERAL] postgresql referencing and creating types as record
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_PAYMENTinCUM_A_TYPES. LT_PAYMENT out_se_ce_hashmapout 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.kla...@aklaver.com mailto:adrian.kla...@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 (BASICnumber, DPnumber, GRADE_PAYnumber ); 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 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 CREATE FUNCTION http://www.postgresql.org/__docs/9.3/interactive/sql-__createfunction.html http://www.postgresql.org/docs/9.3/interactive/sql-createfunction.html pl/pgsql http://www.postgresql.org/__docs/9.3/interactive/plpgsql.__html http://www.postgresql.org/docs/9.3/interactive/plpgsql.html In particular: http://www.postgresql.org/__docs/9.3/interactive/plpgsql-__declarations.html http://www.postgresql.org/docs/9.3/interactive/plpgsql-declarations.html If you want a more specific answer, we will need more specific information. THXRGDS 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 Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Adrian Klaver adrian.kla...@aklaver.com mailto:adrian.kla...@aklaver.com -- Regards Vaishali Maheshkar -- Adrian Klaver adrian.kla...@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
Re: [GENERAL] postgresql referencing and creating types as record
On 08/07/2014 09:50 PM, vpmm2007 wrote: create or replace package CUM_A_TYPES as type LT_PAYMENT is record (BASICnumber, DPnumber, GRADE_PAYnumber ); 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/ THXRGDS VPMM -- Adrian Klaver adrian.kla...@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
Re: [GENERAL] postgresql referencing and creating types as record
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 (BASICnumber, DPnumber, GRADE_PAYnumber ); 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. THXRGDS 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.kla...@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
Re: [GENERAL] postgresql referencing and creating types as record
create or replace package CUM_A_TYPES as type LT_PAYMENT is record (BASICnumber, DPnumber, GRADE_PAYnumber ); 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 THXRGDS 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
Re: [GENERAL] postgresql referencing and creating types as record
In reply to this post by vpmm2007 create or replace package CUM_A_TYPES as type LT_PAYMENT is record (BASICnumber, DPnumber, GRADE_PAYnumber ); 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 THXRGDS 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
Re: [GENERAL] 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
