[SQL] any one can help me how to get multiple rows in postgresql using arrays in functions
any one can help me how to get multiple rows in postgresql using arrays in functions Thanks & Regards Penchal reddy | Software Engineer Infinite Computer Solutions | Exciting Times…Infinite Possibilities... SEI-CMMI level 5 | ISO 9001:2000 IT SERVICES | BPO Telecom | Finance | Healthcare | Manufacturing | Energy & Utilities | Retail & Distribution | Government Tel +91-80-4133-(Ext:503)| Fax +91-80-51930009 | Cell No +91-9886774209|www.infics.com Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and/ or its Customers and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at [EMAIL PROTECTED] and delete this mail from your records.
[SQL] set return function is returning a single record, multiple times,how can i get all the records in the table( description inside )
Hi Please spare some time to provide a solution for the described problem : I am using set returning functions to return all the records from a table named pss , But what I am getting is the first record is returned as many times , the number of records present in the rank_master: I am giving a detailed description below please check it out 1) The following query creates pss table: create table pss( name varchar(20), num integer, phno integer ); 2) insert three records in to pss : insert into pss values(‘penchal’,1,420); insert into pss values(‘joe’,2,421); insert into pss values(‘ali’,3,422); 3) create an user defines type of variable named structrankmaster2 ( something like a structure to hold a record ) : create type Structrankmaster2 as (name varchar(20), num integer, phno integer); 4) The following is the function that retrieves the records from pss : CREATE or replace FUNCTION ftoc9() RETURNS setof structrankmaster2 LANGUAGE 'plpgsql' AS' DECLARE rowdata pss%rowtype; BEGIN for i in 1..3 loop select * into rowdata from pss ; return next rowdata ; end loop; return; end'; 5) now call the function from command prompt: Select * from ftoc9(); 6) the following is the output that I am getting ( i.e the first row repeated 3 times ) : name | num | phno -+-+-- penchal | 1 | 420 penchal | 1 | 420 penchal | 1 | 420 (3 rows) 7) what exactly I should be getting is : name | num | phno -+-+-- penchal | 1 | 420 joe | 2 | 421 penchal | 3 | 422 (3 rows) Please provide a solution for this so that I can get name | num | phno -+-+-- penchal | 1 | 420 joe | 2 | 421 penchal | 3 | 422 (3 rows) Thanks & regards Thanks & Regards Penchal reddy | Software Engineer Infinite Computer Solutions | Exciting Times…Infinite Possibilities... SEI-CMMI level 5 | ISO 9001:2000 IT SERVICES | BPO Telecom | Finance | Healthcare | Manufacturing | Energy & Utilities | Retail & Distribution | Government Tel +91-80-4133-(Ext:503)| Fax +91-80-51930009 | Cell No +91-9886774209|www.infics.com Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and/ or its Customers and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at [EMAIL PROTECTED] and delete this mail from your records.
[SQL] i am getting error when i am using copy command
Hi .. 1) I created one table 2) Create table penchal(id integer, name varchar(12),age integer); 3) Then I inserted some values into this table……… 4) Insert into penchal values(1,’reddy’,2); 5) Select * from penchal ; I used this statement to display the values in a table 6) Finaly I want to use copy …. 7) But I am getting error when I am using this copy statement Copy penchal to ‘/tmp/penchal.out’ When I am using this statement I am getting error is : could not open file ‘/tmp/penchal.out’ for writing: no such file or directory.. May I know y I am getting this error…….. pls any one can help me for this.. Thanks & Regards Penchal reddy | Software Engineer Infinite Computer Solutions | Exciting Times…Infinite Possibilities... SEI-CMMI level 5 | ISO 9001:2000 IT SERVICES | BPO Telecom | Finance | Healthcare | Manufacturing | Energy & Utilities | Retail & Distribution | Government Tel +91-80-4133-(Ext:503)| Fax +91-80-51930009 | Cell No +91-9886774209|www.infics.com Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and/ or its Customers and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at [EMAIL PROTECTED] and delete this mail from your records.
[SQL] i need information regarding schema
Hi good morning to every one….. I am using pgAdmin to gui for postgresql…I want to know how to create schema in pgAdmin and how to set roles ……. Thanks & Regards Penchal reddy | Software Engineer Infinite Computer Solutions | Exciting Times…Infinite Possibilities... SEI-CMMI level 5 | ISO 9001:2000 IT SERVICES | BPO Telecom | Finance | Healthcare | Manufacturing | Energy & Utilities | Retail & Distribution | Government Tel +91-80-4133-(Ext:503)| Fax +91-80-51930009 | Cell No +91-9886774209|www.infics.com Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and/ or its Customers and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at [EMAIL PROTECTED] and delete this mail from your records.
[SQL] some error when executing query in pgAdmin tool
I created one table in pgAdmin tool but when I am executing
query it is giving error….
CREATE TABLE "ADV"
(
"T-Section_Id" varchar(10) NOT NULL,
"CDA_No" varchar(7) NOT NULL,
"Imp_Schedule_Id" int4 NOT NULL,
"Sanction_No" varchar(20) NOT NULL,
"Sanction_Date" date NOT NULL,
"Station_From" varchar(20) NOT NULL,
"Station_To" varchar(20) NOT NULL,
"Amt_Claimed" int4,
"Amt_Admitted" int4,
"Dak_Id" varchar(20) NOT NULL,
"Refund_Dak_Id" int4 NOT NULL,
"T-Wing_Allowance_Id" varchar(10) NOT NULL,
CONSTRAINT "ADV_pkey" PRIMARY KEY
("T-Section_Id")
)
WITHOUT OIDS;
ALTER TABLE "ADV" OWNER TO postgres;
Above table I created…..
Select * from ADV; when I am executing this query I am
getting error is
Relation ADV does not exist… like this error is giving
… may I know y am I getting this error
Thanks & Regards
Penchal reddy | Software Engineer
Infinite Computer Solutions | Exciting Times…Infinite Possibilities...
SEI-CMMI level 5 | ISO 9001:2000
IT SERVICES |
BPO
Telecom | Finance
| Healthcare | Manufacturing
| Energy & Utilities | Retail
& Distribution | Government
Tel +91-80-5193-(Ext:503)| Fax +91-80-51930009 | Cell No +91-9980012376|www.infics.com
Information transmitted by this e-mail is
proprietary to Infinite Computer Solutions and/ or its Customers and is
intended for use only by the individual or entity to which it is addressed, and
may contain information that is privileged, confidential or exempt from
disclosure under applicable law. If you are not the intended recipient or it
appears that this mail has been forwarded to you without proper authority, you
are notified that any use or dissemination of this information in any manner is
strictly prohibited. In such cases, please notify us immediately at [EMAIL PROTECTED] and delete this mail from your records.
[SQL] hi can u give solution to this query
Hi sir…
If u don’t mind can u reply for this question
This is one table… it has one primary key…….so
I can insert the values from data edit…
1)…CREATE TABLE "ADV"
(
"T-Section_Id" varchar(10) NOT NULL,
"CDA_No" varchar(7) NOT NULL,
"Imp_Schedule_Id" int4 NOT NULL,
"Sanction_No" varchar(20) NOT NULL,
"Sanction_Date" date NOT NULL,
"Station_From" varchar(20) NOT NULL,
"Station_To" varchar(20) NOT NULL,
"Amt_Claimed" int4,
"Amt_Admitted" int4,
"Dak_Id" varchar(20) NOT NULL,
"Refund_Dak_Id" int4 NOT NULL,
"T-Wing_Allowance_Id" varchar(10) NOT NULL,
CONSTRAINT "ADV_pkey" PRIMARY KEY
("T-Section_Id")
)
WITHOUT OIDS;
ALTER TABLE "ADV" OWNER TO postgres;
2) this table doesn’t contain
primary key … but I cant insert values from data edit…
CREATE TABLE "ADV_LTC"
(
"T-Section_Id" varchar(20) NOT
NULL,
"Ltc_Block_Year" date NOT
NULL,
"Ltc_Type" varchar(10) NOT
NULL,
"Leave_Period_From" date NOT
NULL,
"Leave_Period_To" date NOT
NULL,
"Date_Journey" date NOT NULL,
"Travelling" varchar(10) NOT
NULL,
"No_Child" int4,
"No_Adult" int4,
"Child_Fare" int4,
"Adult_Fare" int4,
"Journey_Fare" int4,
"Amt_Clm" int4 NOT NULL,
"Amt_Adm" int4,
"Remarks" varchar(40)
)
WITHOUT OIDS;
ALTER TABLE "ADV_LTC" OWNER TO
postgres;
Can u check it y I am unable to insert
values to 2nd table……….
Thanks & Regards
Penchal reddy | Software Engineer
Infinite Computer Solutions | Exciting Times…Infinite Possibilities...
SEI-CMMI level 5 | ISO 9001:2000
IT SERVICES |
BPO
Telecom | Finance
| Healthcare | Manufacturing
| Energy & Utilities | Retail
& Distribution | Government
Tel +91-80-5193-(Ext:503)| Fax +91-80-51930009 | Cell No +91-9980012376|www.infics.com
Information transmitted by this e-mail is
proprietary to Infinite Computer Solutions and/ or its Customers and is
intended for use only by the individual or entity to which it is addressed, and
may contain information that is privileged, confidential or exempt from
disclosure under applicable law. If you are not the intended recipient or it
appears that this mail has been forwarded to you without proper authority, you
are notified that any use or dissemination of this information in any manner is
strictly prohibited. In such cases, please notify us immediately at [EMAIL PROTECTED] and delete this mail from your records.
[SQL] hi how to use encryption for incomtax in postgresql
hi sir how to use encryption to password in postgresql with examples Thanks & Regards Penchal reddy | Software Engineer Infinite Computer Solutions | Exciting Times…Infinite Possibilities... SEI-CMMI level 5 | ISO 9001:2000 IT SERVICES | BPO Telecom | Finance | Healthcare | Manufacturing | Energy & Utilities | Retail & Distribution | Government Tel +91-80-5193-(Ext:503)| Fax +91-80-51930009 | Cell No +91-9980012376|www.infics.com Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and/ or its Customers and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at [EMAIL PROTECTED] and delete this mail from your records.
[SQL] hi still i am unable to provide encryption to a particular table....
Hi I have gone thru encryption in postgresql help… but there I got some message like set some comments in pg_hba.conf file.. if I set host name and port number here wt I will get… my requirement is to do encryption to one of my table.. if any body knows about this please help me…….. Thanks & Regards Penchal reddy | Software Engineer Infinite Computer Solutions | Exciting Times…Infinite Possibilities... SEI-CMMI level 5 | ISO 9001:2000 IT SERVICES | BPO Telecom | Finance | Healthcare | Manufacturing | Energy & Utilities | Retail & Distribution | Government Tel +91-80-5193-(Ext:503)| Fax +91-80-51930009 | Cell No +91-9980012376|www.infics.com Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and/ or its Customers and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at [EMAIL PROTECTED] and delete this mail from your records.
[SQL] hi i need to encrypt one field in a table
Hello,I want to create a table with an encrypted field for a password entry, sothat you can see only .And can I encrypt fields which datatype is an integer or a timestamp? Hi pls let me know this……….. Thanks & Regards Penchal reddy | Software Engineer Infinite Computer Solutions | Exciting Times…Infinite Possibilities... SEI-CMMI level 5 | ISO 9001:2000 IT SERVICES | BPO Telecom | Finance | Healthcare | Manufacturing | Energy & Utilities | Retail & Distribution | Government Tel +91-80-5193-(Ext:503)| Fax +91-80-51930009 | Cell No +91-9980012376|www.infics.com Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and/ or its Customers and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at [EMAIL PROTECTED] and delete this mail from your records.
[SQL] i need solution to this problem
Hi good morning to all…….. I have tables like 1) emp_table (personal_no integer (foreign key), cdacno varchar (primary key),name varchar); 2) Rank_table (rank_id varchar (primary key), rank_name varchar); 3) Rank_date_table (rank_id (foreign key), rank_date date); 4) Unit_table (unit_id varchar (primarykey), unit_name varchar); 5) Personal_table (per_no varchar (primary key), pername varchar); My query is ….if I give cdacno I have to get per_no from personal_table.. With this I have to display rank_name from rank_table ,name from emp_table, unit_name from unit_master.. Like that if I give per_no I have to get cdacno from emp_table.. .. With this I have to display rank_name from rank_table ,name from emp_table, unit_name from unit_master.. Doing these things first it has to check when we r giving cdacno.. whether per_no is null or not.. like this if I give per_no it has to check cdacno is null or not. Let me know the solution.. But I written one function to this to get per_no if I give cdacno………. Thanks & Regards Penchal reddy | Software Engineer Infinite Computer Solutions | Exciting Times…Infinite Possibilities... SEI-CMMI level 5 | ISO 9001:2000 IT SERVICES | BPO Telecom | Finance | Healthcare | Manufacturing | Energy & Utilities | Retail & Distribution | Government Tel +91-80-5193-(Ext:503)| Fax +91-80-51930009 | Cell No +91-9980012376|www.infics.com Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and/ or its Customers and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at [EMAIL PROTECTED] and delete this mail from your records. Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and / or its Customers and is intended for use only by the individual or the entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at [EMAIL PROTECTED] and delete this email from your records.
[SQL] can any one solve this problem
emp_table( Cdacno varchar (7) (primary key), Personal_No varchar (10)(foreign key), Name varchar (40)); personal_table ( Personal_No varchar (10) (primary key), Cdacno varchar (7), Date_Of_Birth date); unit_master ( Unit id varchar (10) (primary key), Unit_Name varchar(25), Unit_Location varchar(25)); Unit_Details_table ( Unit_id varchar (foreign key) CDA_No varchar(7) foreign key); rank_table( Rank_ID numeric(2)(primary key), Rank_Code numeric(2), Rank_Name varchar (25)); Rank_Date_table ( Rank_Date__ID numeric NOT NULL, CDA_No varchar(7) (foreign key), Rank_ID numeric(2)); My query is ….if I give cdacno I have to get per_no from personal_table.. With this I have to display rank_name from rank_table ,name from emp_table, unit_name from unit_master.. Like that if I give per_no I have to get cdacno from emp_table.. .. With this I have to display rank_name from rank_table ,name from emp_table, unit_name from unit_master.. And here unit_name is depends on unit_details_table ..and rank_name is depends on rank_date_table.. Doing these things first it has to check when we r giving cdacno.. whether per_no is null or not.. like this if I give per_no it has to check cdacno is null or not. Let me know the solution.. But I written one function to this to get per_no if I give cdacno………. Thanks & Regards Penchal reddy | Software Engineer Infinite Computer Solutions | Exciting Times…Infinite Possibilities... SEI-CMMI level 5 | ISO 9001:2000 IT SERVICES | BPO Telecom | Finance | Healthcare | Manufacturing | Energy & Utilities | Retail & Distribution | Government Tel +91-80-5193-(Ext:503)| Fax +91-80-51930009 | Cell No +91-9980012376|www.infics.com Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and/ or its Customers and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at [EMAIL PROTECTED] and delete this mail from your records. Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and / or its Customers and is intended for use only by the individual or the entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at [EMAIL PROTECTED] and delete this email from your records.
[SQL] hi i need to connect database from my system to another system
Hi good afternoon to every one.. I am using postgresql database server… in my system.. if I want to connect to this database server from other system..Or how can I use this database server in other machine .. How can I do this..if any one have idea about this please give me brief description Thanks & Regards Penchal reddy | Software Engineer Infinite Computer Solutions | Exciting Times…Infinite Possibilities... SEI-CMMI level 5 | ISO 9001:2000 IT SERVICES | BPO Telecom | Finance | Healthcare | Manufacturing | Energy & Utilities | Retail & Distribution | Government Tel +91-80-5193-(Ext:503)| Fax +91-80-51930009 | Cell No +91-9980012376|www.infics.com Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and/ or its Customers and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at [EMAIL PROTECTED] and delete this mail from your records. Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and / or its Customers and is intended for use only by the individual or the entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at [EMAIL PROTECTED] and delete this email from your records.
Re: [SQL] hi i need to connect database from my system to another
Hi I am using windows xp operating system... I want to know how to connect remote postgresql from my system.. I need brief description from beginning.. Now I am using pg_HBF conf file to connect remote postgresql in that I am giving host name , ip-address,mask and trust... but it is showing error like Could not connect to server: connection refused(0x274d\10061) Is the server running on host 127.0.0.1 and accepting TCP\IP connection on port 5432 ? Regards penchal -Original Message- From: Richard Broersma Jr [mailto:[EMAIL PROTECTED] Sent: Monday, July 03, 2006 5:57 PM To: Penchalaiah P. Cc: General Postgresql List Subject: RE: [SQL] hi i need to connect database from my system to another system > If u don't mind can u please tell me clearly.. where I have start -I or > how to start postgresql with -I > > I am using postgresql database server... in my system.. if I want to > > connect to this database server from other system..Or how can I use > this > > database server in other machine .. > > > > How can I do this..if any one have idea about this please give me > brief > > description > > You need to start the postgresql server with the "-i" option to allow > TCP/IP connections. Then > you need to configure your pg_hba.conf file to set the permissions about > which users can connect > to which databases. > > Notice the pg_ctl section for starting postgresql. > http://www.postgresql.org/docs/8.1/interactive/app-pg-ctl.html > > Notice the -o "options" section that can be set. (in this case you want > -i) > http://www.postgresql.org/docs/8.1/interactive/app-postmaster.html > > Finally notice, the section on the pg_hba.conf file. > http://www.postgresql.org/docs/8.1/interactive/client-authentication.htm > l#AUTH-PG-HBA-CONF > > Also, secure shell also works well when connecting from another system. Don't forget to include the pgsql-sql list in your reply's. :-) The individuals on this list have varying degree of experience and knowledge of postgresql. So if you only respond to me, you would be limiting yourself to my meager experience and knowledge. ;-) To start off with, it would be helpful to know which OS version of PostgreSQL you are using. The windows versions is pre-configured to start with the "-i" option for allowing TCP/IP connections. Secondly, how are you trying to connect to your postgresql server from another system? What error messages are you getting. Regards, Richard Broersma Jr. Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and / or its Customers and is intended for use only by the individual or the entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at [EMAIL PROTECTED] and delete this email from your records. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] i have a problem of privilages
Hi good afternoon to every one.. Now I am accessing remote database server in postgresql.. but I can see all tables and functions in remote database server but I can delete,update,alter and drop tables in remote database server from my local machine.. to avoid these things wt I have to do… Just they have to see my tables and structure.. and they cant do any more operations on the tables.. to avoid this wt can I do please tell me solution … Thanks & Regards Penchal reddy | Software Engineer Infinite Computer Solutions | Exciting Times…Infinite Possibilities... SEI-CMMI level 5 | ISO 9001:2000 IT SERVICES | BPO Telecom | Finance | Healthcare | Manufacturing | Energy & Utilities | Retail & Distribution | Government Tel +91-80-5193-(Ext:503)| Fax +91-80-51930009 | Cell No +91-9980012376|www.infics.com Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and/ or its Customers and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at [EMAIL PROTECTED] and delete this mail from your records. Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and / or its Customers and is intended for use only by the individual or the entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at [EMAIL PROTECTED] and delete this email from your records.
Re: [SQL] i have a problem of privilages
I have 290 tables .. to these tables one schema is there .. that name is public….so I don’t know how to create permission to these tables.. either I have to give permissions to individual table.. or I have to give permissions to schema or schema name… if I give permission to schema it has to applicable to all tables.. I created one user with password… then finally I don’t know how to allocate permission to him.. please tell me very briefly step by step.. then I can understand… Please tell me if any one knows about this…….. From: MJ Santhosh [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 04, 2006 5:47 PM To: Penchalaiah P. Cc: pgsql-sql@postgresql.org; Richard Broersma Jr Subject: Re: [SQL] i have a problem of privilages create a user with limited permmission and give the same to the remote users. May be that will solve your problem. REVOKE INSERT,UPDATE,DELETE ON welcome from PUBLIC; On 7/4/06, Penchalaiah P. <[EMAIL PROTECTED]> wrote: Hi good afternoon to every one.. Now I am accessing remote database server in postgresql.. but I can see all tables and functions in remote database server but I can delete,update,alter and drop tables in remote database server from my local machine.. to avoid these things wt I have to do… Just they have to see my tables and structure.. and they cant do any more operations on the tables.. to avoid this wt can I do please tell me solution … Thanks & Regards Penchal reddy | Software Engineer Infinite Computer Solutions | Exciting Times…Infinite Possibilities... SEI-CMMI level 5 | ISO 9001:2000 IT SERVICES | BPO Telecom | Finance | Healthcare | Manufacturing | Energy & Utilities | Retail & Distribution | Government Tel +91-80-5193-(Ext:503) | Fax +91-80-51930009 | Cell No +91-9980012376 | www.infics.com Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and/ or its Customers and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at [EMAIL PROTECTED] and delete this mail from your records. Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and / or its Customers and is intended for use only by the individual or the entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at [EMAIL PROTECTED] and delete this email from your records. -- Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and / or its Customers and is intended for use only by the individual or the entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at [EMAIL PROTECTED] and delete this email from your records.
[SQL] above the date functionssssssss
Hi good evening to every one……… i want to get month and year in the current date..see this is date 2005-02-12... here date is 12 , month is 02 and year is 2005..but i want format like 02-2005... can u tell me how to do this Thanks & Regards Penchal reddy | Software Engineer Infinite Computer Solutions | Exciting Times…Infinite Possibilities... SEI-CMMI level 5 | ISO 9001:2000 IT SERVICES | BPO Telecom | Finance | Healthcare | Manufacturing | Energy & Utilities | Retail & Distribution | Government Tel +91-80-5193-(Ext:503)| Fax +91-80-51930009 | Cell No +91-9980012376|www.infics.com Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and/ or its Customers and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at [EMAIL PROTECTED] and delete this mail from your records. Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and / or its Customers and is intended for use only by the individual or the entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at [EMAIL PROTECTED] and delete this email from your records.
[SQL] hi let me know the solution to this question
Hi good morning all…….. Can u let me know if at all any system defined variable exists to check whether insert , update , and delete is successfully executed in a stored procedure…….. So that I can perform some steps based on the result of execution if possible please send me the sample code… Thanks & Regards Penchal reddy | Software Engineer Infinite Computer Solutions | Exciting Times…Infinite Possibilities... SEI-CMMI level 5 | ISO 9001:2000 IT SERVICES | BPO Telecom | Finance | Healthcare | Manufacturing | Energy & Utilities | Retail & Distribution | Government Tel +91-80-5193-(Ext:503)| Fax +91-80-51930009 | Cell No +91-9980012376|www.infics.com Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and/ or its Customers and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at [EMAIL PROTECTED] and delete this mail from your records. Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and / or its Customers and is intended for use only by the individual or the entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at [EMAIL PROTECTED] and delete this email from your records.
[SQL] Problem while using lo_import and lo_export
Hi,
I have PostgresSQL database connection from server. Server
ip is 172.16.5.179
Now I want use lo_import and lo_export function for storing
images into the database.
This images is located in my system itself.
When I am going using the following query…
Insert into image
values('chanukya',lo_import('D:/Vivek/Personal/PICS/IN/chanukya.jpg'))
….it rises error ‘no such file or
directory’
But the same query executing successfully in my local
database…
Could please tell me to resolve this problem…
Thanks & Regards
Penchal reddy | Software Engineer
Infinite Computer Solutions | Exciting Times…Infinite Possibilities...
SEI-CMMI level 5 | ISO 9001:2000
IT SERVICES |
BPO
Telecom | Finance
| Healthcare | Manufacturing
| Energy & Utilities | Retail
& Distribution | Government
Tel +91-80-5193-(Ext:503)| Fax +91-80-51930009 | Cell No +91-9980012376|www.infics.com
Information transmitted by this e-mail is
proprietary to Infinite Computer Solutions and/ or its Customers and is
intended for use only by the individual or entity to which it is addressed, and
may contain information that is privileged, confidential or exempt from
disclosure under applicable law. If you are not the intended recipient or it
appears that this mail has been forwarded to you without proper authority, you
are notified that any use or dissemination of this information in any manner is
strictly prohibited. In such cases, please notify us immediately at [EMAIL PROTECTED] and delete this mail from your records.
Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and / or its Customers and is intended for use only by the individual or the entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at [EMAIL PROTECTED] and delete this email from your records.
[SQL] How to use table from one database to another
Hai all, I have 2 databases namee PAO and CAS. PAO contains 3 schemas named Public,pao,sts CAS contains 4 schemas named Public,cao,sts,reports Now i am in PAO database..now i want access table 'activity' in schema 'cas' in CAS database. How it is posible. 2nd thing is... i have 2 servers access i.e local and mainserver. How access table from one server to another server? please tel me...because we need this one Please give me full details with examples… because I am new to Postgres but I know oracle very well Thanks & Regards Penchal reddy | Software Engineer Infinite Computer Solutions | Exciting Times…Infinite Possibilities... SEI-CMMI level 5 | ISO 9001:2000 IT SERVICES | BPO Telecom | Finance | Healthcare | Manufacturing | Energy & Utilities | Retail & Distribution | Government Tel +91-80-5193-(Ext:503)| Fax +91-80-51930009 | Cell No +91-9980012376|www.infics.com Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and/ or its Customers and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at [EMAIL PROTECTED] and delete this mail from your records. Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and / or its Customers and is intended for use only by the individual or the entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at [EMAIL PROTECTED] and delete this email from your records.
[SQL] How to cal function in one another
hai, In mainfun,the query returns more than two rows,if execute query outside. I have to remove limit keyword in mainfun defination...even it returns 1st row values only..But i need the multful rows? so how can i do that...please help me... select subfun1(22); subfun1 - (swarna,23) select mainfun(22); (2600,10) query result outside.. select sal,dept into return_rec from emp where mgr=23 -- here i removed the limit keyword in mainfun definition 2600,10 3500,20 6520,10 I am waiting for ur positive reply... thanks & Regards Penchal reddy | Software Engineer Infinite Computer Solutions | Exciting Times…Infinite Possibilities... SEI-CMMI level 5 | ISO 9001:2000 IT SERVICES | BPO Telecom | Finance | Healthcare | Manufacturing | Energy & Utilities | Retail & Distribution | Government Tel +91-80-5193-(Ext:503)| Fax +91-80-51930009 | Cell No +91-9980012376|www.infics.com Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and/ or its Customers and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at [EMAIL PROTECTED] and delete this mail from your records. Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and / or its Customers and is intended for use only by the individual or the entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at [EMAIL PROTECTED] and delete this email from your records.
[SQL] i need informarion regarding functions and arrays...
Hi good evening to all…….. Rcid value 1 yes 2 no 3 yes 4 yes 5 no 6 yes 7 no 8 yes 9 no 10 yes 11 no 12 no this is the front end screen they will enter all these 12 in values… then they will press save button… these values have to be store in rnt table… can u tell me how to store all these 12 values at a time in rnt table… but I written one function for this but it is accepting only one value at a time to insert… this is the function… create or replace function rentfunc(in_rcid int4,in_value varchar) as ‘ begin insert into rnt(racid,auditorvalue) values ($1,$2);end;‘language’plpgsql’; If any one knows how to store all these 12 values in a rnt table… let me know ….. Thanks & Regards Penchal reddy | Software Engineer Infinite Computer Solutions | Exciting Times…Infinite Possibilities... SEI-CMMI level 5 | ISO 9001:2000 IT SERVICES | BPO Telecom | Finance | Healthcare | Manufacturing | Energy & Utilities | Retail & Distribution | Government Tel +91-80-5193-(Ext:503)| Fax +91-80-51930009 | Cell No +91-9980012376|www.infics.com Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and/ or its Customers and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at [EMAIL PROTECTED] and delete this mail from your records. Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and / or its Customers and is intended for use only by the individual or the entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at [EMAIL PROTECTED] and delete this email from your records.
[SQL] problem with sequence.....
Hi good evening every one………
I have a problem with sequence ..
I created one sequence with the name of famaseq..
I am using this sequence in my function..
This is the function
Create or replace function getFamanewcase1(in_cda_no
varchar,in_dak_id varchar,in_name_of_beneficiary varchar,in_relation varchar,
In_address varchar,in_amnt_athrzd int4,in_pay_through
varchar,in_bank_code varchar,in_bank_acc_no varchar,in_amnt_comision int4,
In_from_date date,in_mo_comisionpaid bool,in_remrks varchar)
returns integer as'
Declare
Pay_thrw varchar:=''bank'';
Begin
If pay_thrw =$7 then
Insert into family_allotment_trans (cda_no, dak_id, name_of_the_beneficiary,
relation, address, amount_authorized,
pay_through,bank_code,bank_acc_no,amount_of_comision,from_date,mo_comsn_paidby,remarks,famly_alltmnt_trans_id)
values($1,$2,$3,$4,$5,$6,$7,
$8,$9,$10,$11,$12,$13,nextval(''famaseq''));
else
Insert into family_allotment_trans (cda_no,dak_id,name_of_the_beneficiary,relation,address,amount_authorized,pay_through,bank_code,
amount_of_comision,from_date,mo_comsn_paidby,remarks,famly_alltmnt_trans_id)
values($1,$2,$3,$4,$5,$6,$7,$8,$10,$11,$12,$13,nextval(''famaseq''));
end if;
return 0;
end;
'language'plpgsql';
select
getFamanewcase1('123456a','dak1','penchal','friend','bangalore',2000,'order','bc2','bc1',20,'2006-06-06','false','no
remarks');
If I use this select statement with correct inputs that
sequence is working properly..
But when I was passing wrong values to that function..Automaticaly
sequence is creating but its not showing in view data..
If again I pass correct values to that function that values
can see in view data with increment value of sequence
Any one can tell me how to stop that sequence value when
ever I was passing wrong values to that function….
2 bc11 23456a dak2 vivek brother hyd 333 2006-06-25 bc2 f
4 bc2 123456a dak1 penchal friend bangalore 2006-08-08 2000 2006-06-06 bc1
false f no remarks 20
Actually second record has starts with 3 but here its
showing 4 becs before passing values to second record I given wrong values but sequence
Is incremented automaricaly..
Can u telll me when ever I am going to give wrong values to
that function .. the sequence value must be same………
Thanks & Regards
Penchal reddy | Software Engineer
Infinite Computer Solutions | Exciting Times…Infinite Possibilities...
SEI-CMMI level 5 | ISO 9001:2000
IT SERVICES |
BPO
Telecom | Finance
| Healthcare | Manufacturing
| Energy & Utilities | Retail
& Distribution | Government
Tel +91-80-5193-(Ext:503)| Fax +91-80-51930009 | Cell No +91-9980012376|www.infics.com
Information transmitted by this e-mail is
proprietary to Infinite Computer Solutions and/ or its Customers and is
intended for use only by the individual or entity to which it is addressed, and
may contain information that is privileged, confidential or exempt from
disclosure under applicable law. If you are not the intended recipient or it
appears that this mail has been forwarded to you without proper authority, you
are notified that any use or dissemination of this information in any manner is
strictly prohibited. In such cases, please notify us immediately at [EMAIL PROTECTED] and delete this mail from your records.
Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and / or its Customers and is intended for use only by the individual or the entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at [EMAIL PROTECTED] and delete this email from your records.
[SQL] to get DD-MM-YYYY format of data
Hi .. I am using date is data type to one of the field in my table….but when ever I am passing values to that field it is taking yyy-mm-dd format.. But I don’t want like that .. I need like this DD-MM-.. for this wt I have to do… Thanks & Regards Penchal reddy | Software Engineer Infinite Computer Solutions | Exciting Times…Infinite Possibilities... SEI-CMMI level 5 | ISO 9001:2000 IT SERVICES | BPO Telecom | Finance | Healthcare | Manufacturing | Energy & Utilities | Retail & Distribution | Government Tel +91-80-5193-(Ext:503)| Fax +91-80-51930009 | Cell No +91-9980012376|www.infics.com Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and/ or its Customers and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at [EMAIL PROTECTED] and delete this mail from your records. Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and / or its Customers and is intended for use only by the individual or the entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at [EMAIL PROTECTED] and delete this email from your records.
[SQL] hi i am gettin error when i am deleting a function from my pgadmin
Hi good morning to all…. I created some functions in my pgadmin… when I am deleting those functions from that pgadmin its giving error…i.e An ERROR has occurred ERROR:function function_name1(character varying, character varying, character varying, date, character varying) does not exist… May I know the reason y its not deleting…. Thanks & Regards Penchal reddy | Software Engineer Infinite Computer Solutions | Exciting Times…Infinite Possibilities... SEI-CMMI level 5 | ISO 9001:2000 IT SERVICES | BPO Telecom | Finance | Healthcare | Manufacturing | Energy & Utilities | Retail & Distribution | Government Tel +91-80-5193-(Ext:503)| Fax +91-80-51930009 | Cell No +91-9980012376|www.infics.com Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and/ or its Customers and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at [EMAIL PROTECTED] and delete this mail from your records. Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and / or its Customers and is intended for use only by the individual or the entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at [EMAIL PROTECTED] and delete this email from your records.
[SQL] i have table
Hi …
I have one table with 12 fields..
CREATE TABLE addition_alteration_memo
(
addition_alteration_memo int8 NOT NULL DEFAULT
nextval('addition_alteration_memo_addition_alteration_memo_seq'::regclass),
cda_no varchar(7) NOT NULL,
week numeric,
sheet_no numeric,
serial_no numeric,
date date,
dr_no varchar,
amount numeric,
memo_no varchar,
memo_date date,
no_instalments numeric,
instalment_rate numeric)
now I want to add one more field in this table.. but that
field has to come next to cda_no.. I mean as a 3rd field.. If I am
adding that field it is coming last field …
may I know how it is possible to that table…
Thanks & Regards
Penchal reddy | Software Engineer
Infinite Computer Solutions | Exciting Times…Infinite Possibilities...
SEI-CMMI level 5 | ISO 9001:2000
IT SERVICES |
BPO
Telecom | Finance
| Healthcare | Manufacturing
| Energy & Utilities | Retail
& Distribution | Government
Tel +91-80-5193-(Ext:503)| Fax +91-80-51930009 | Cell No +91-9980012376|www.infics.com
Information transmitted by this e-mail is
proprietary to Infinite Computer Solutions and/ or its Customers and is
intended for use only by the individual or entity to which it is addressed, and
may contain information that is privileged, confidential or exempt from
disclosure under applicable law. If you are not the intended recipient or it
appears that this mail has been forwarded to you without proper authority, you
are notified that any use or dissemination of this information in any manner is
strictly prohibited. In such cases, please notify us immediately at [EMAIL PROTECTED] and delete this mail from your records.
Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and / or its Customers and is intended for use only by the individual or the entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at [EMAIL PROTECTED] and delete this email from your records.
[SQL] hi i want help on levels
CREATE TABLE admin_activity ( activity_id int4 NOT NULL, Activity_name varchar NOT NULL, URL varchar, parent_activity_id int4); 1 Administration 0 2 Activities 1 3 Manage Activity 2 4 Map Activity Role 2 5 Allowance 1 6 Bank 1 7 Code Head 1 8 Corps 1 9 DSOP 1 10 Entitlements 1 These r the values to that table… I want like this… tell me the query for this… Administration -> Manage Actvities -> Create Activity è Update Activity è Delete Activity Admin 1 Activity_id activity_name parent_id _ 1 Admin null 2 ` Manage activities 1 3 Create Activity 2 4 Update Activity 2 5 Admin1 null 6 Delete Activity 2 Thanks & Regards Penchal reddy | Software Engineer Infinite Computer Solutions | Exciting Times…Infinite Possibilities... SEI-CMMI level 5 | ISO 9001:2000 IT SERVICES | BPO Telecom | Finance | Healthcare | Manufacturing | Energy & Utilities | Retail & Distribution | Government Tel +91-80-5193-(Ext:503)| Fax +91-80-51930009 | Cell No +91-9980012376|www.infics.com Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and/ or its Customers and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at [EMAIL PROTECTED] and delete this mail from your records. Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and / or its Customers and is intended for use only by the individual or the entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at [EMAIL PROTECTED] and delete this email from your records.
[SQL] hi i am getting error...can u solve this
Hi can u tell me about this problem.. cda_no bp npa rp dp pay_change_date 234597D 7900 400 1000 1000 2006-09-05 234597D 8250 400 1000 1000 2005-09-05 234575E 4000 200 333 111 2004-02-02 234575E 4000 300 333 111 2004-09-02 This is the table it contains some sample data.. but I want to see difference between Thanks & Regards Penchal reddy | Software Engineer Infinite Computer Solutions | Exciting Times…Infinite Possibilities... SEI-CMMI level 5 | ISO 9001:2000 IT SERVICES | BPO Telecom | Finance | Healthcare | Manufacturing | Energy & Utilities | Retail & Distribution | Government Tel +91-80-5193-(Ext:503)| Fax +91-80-51930009 | Cell No +91-9980012376|www.infics.com Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and/ or its Customers and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at [EMAIL PROTECTED] and delete this mail from your records. Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and / or its Customers and is intended for use only by the individual or the entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at [EMAIL PROTECTED] and delete this email from your records.
[SQL] hi may i know y am i getting this error
Hi When I am executing my function its giving error.. first time its running properly.. but when I am executing second time I am getting error create or replace function irla_punching_medium_insert1(in_proj_code numeric,in_pt_code varchar,in_mon_abs date) returns varchar as' declare l_p varchar; credit_amount numeric; debit_amount numeric; l_vr_no varchar; l_count integer; begin select irla_code into l_p from npo_project_irla_code where npo_proj_code=$1 and pt_code=$2; create table dummy_table without oids as select side,irla_code,substr(type_charge,1,1)||substr(type_charge,2,1) || substr(type_charge,4,1) ||substr(type_charge,5,1) as type_charge,npo_code from irla_npo_pm_codes_master where type_charge=''(+-)CR'' or type_charge=''(+-)RC'' union all select side,irla_code,substr(type_charge,1,1)||substr(type_charge,3,1)||substr( type_charge,4,1) || substr(type_charge,6,1) as type_charge,npo_code from irla_npo_pm_codes_master where type_charge=''(+-)CR'' or type_charge=''(+-)RC'' union all select side,irla_code,type_charge,npo_code from irla_npo_pm_codes_master where type_charge=''(+)C'' or type_charge=''(+)R'' ; update dummy_table set irla_code=l_p where irla_code=''P''; create table amount_table without oids as select dt.side,dt.irla_code,sum(npoaa.amount) as amount,dt.type_charge from npo_abs_amount npoaa,dummy_table dt where dt.side=npoaa.side and dt.npo_code=npoaa.npo_code and extract(month from to_date(npoaa.mon_abs,''-mm-dd''))||''/''|| extract(year from to_date(npoaa.mon_abs,''-mm-dd'')) =extract(month from to_date($1,''-mm-dd''))||''/''|| extract(year from to_date($1,''-mm-dd'')) and npoaa.proj_code=$1 and npoaa.pt_code= $2 group by dt.irla_code ,dt.type_charge,dt.side ; drop table dummy_table; select cast(substr(max(vr_no),1,2) || cast(substr(max(vr_no),3,1) as integer)+1 as varchar) into l_vr_no from irla_punchingmedium_top_amt ; select sum(amount) into credit_amount from amount_table where side=''C''; select sum(amount) into debit_amount from amount_table where side=''D''; insert into irla_punchingmedium_top_amt (vr_no,credit_tot_amt,class_of_vr,mon_abs,proj_code,pt_code,debit_tot_am t)values (l_vr_no,credit_amount,1,$3,$1,$2,debit_amount); insert into irla_punchingmedium_amt (amount,codehead,type_of_charge,vr_no) select amount,irla_code,type_charge,(select l_vr_no) from amount_table; raise notice''%'', l_vr_no; drop table amount_table; raise notice ''%2'', l_count; return ''inserted''; end; 'language'plpgsql'; ERROR: relation with OID 75275 does not exist CONTEXT: SQL statement "update dummy_table set irla_code= $1 where irla_code='P'" PL/pgSQL function "irla_punching_medium_insert1" line 19 at SQL statement Thanks & Regards Penchal reddy | Software Engineer Infinite Computer Solutions | Exciting Times...Infinite Possibilities... SEI-CMMI level 5 | ISO 9001:2000 IT SERVICES | BPO Telecom | Finance | Healthcare | Manufacturing | Energy & Utilities | Retail & Distribution | Government Tel +91-80-5193-(Ext:503)| Fax +91-80-51930009 | Cell No +91-9980012376|www.infics.com Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and/ or its Customers and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at [EMAIL PROTECTED] and delete this mail from your records. Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and / or its Customers and is intended for use only by the individual or the entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at [EMAIL PROTECTED] and delete this email from your records.
[SQL] hi any one can help me
Hi ... I installed postgresql 8.1 .. then I uninstalled postgresql.. Now again I am re installing the same postgresql 8.1 .. but its not installing .its giving error like already user exists its showing... I removed those files from registry also.. but I am getting same error... Any one can help to reinstall my postgresql in my machine... Thanks & Regards Penchal reddy | Software Engineer Infinite Computer Solutions | Exciting Times...Infinite Possibilities... SEI-CMMI level 5 | ISO 9001:2000 IT SERVICES | BPO Telecom | Finance | Healthcare | Manufacturing | Energy & Utilities | Retail & Distribution | Government Tel +91-80-5193-(Ext:503)| Fax +91-80-51930009 | Cell No +91-9980012376|www.infics.com Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and/ or its Customers and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at [EMAIL PROTECTED] and delete this mail from your records. Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and / or its Customers and is intended for use only by the individual or the entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at [EMAIL PROTECTED] and delete this email from your records.
Re: [SQL] sql
Hi... I tried this its working.. can u please check this. select count(*) as all, sum(decode(entry_user_id,'VC',1)) as entry_user_id,sum(decode(entry_user_id,'VE',1)) as VE ,sum(decode(entry_user_id,CV,1))as CV,sum(decode(entry_user_id,'SC',1))as SC from vigilance_master; regards penchal -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Karthikeyan Sundaram Sent: Tuesday, February 06, 2007 12:47 PM To: [EMAIL PROTECTED]; pgsql-sql@postgresql.org Subject: Re: [SQL] sql try this: select entry_user_id, sum(decode(entry_user_id,'VC',1,0) as vc, sum(decode(entry_user_id,'VE',1,0) as ve, sum(decode(entry_user_id,'CV',1,0) as cv, sum(decode(entry_user_id,'SC',1,0) as SC from vigilance_master group where entry_user_id=78 group by entry_user_id >From: "Shyju Narayanan" <[EMAIL PROTECTED]> >To: pgsql-sql@postgresql.org >Subject: [SQL] sql >Date: Fri, 2 Feb 2007 13:09:09 +0530 > >Hi All > >this is my table ; >| ID|entry_user_id_int | category_id_chv | >-- >| 1|78|CV | >-- >| 2|78|VC | >-- >| 3|78|CV| >-- >| 4|78|CV| >-- >| 5|78|CV| >-- >| 6|78|CV| >-- >| 7|78|CV| >-- >| 8|78|CV| >-- >| 9|78|VE| >-- >| 10|78|CV| >-- >| 11|78|SC| >-- > >WHEN "select entry_user_id_int, category_id_chv,count(category_id_chv) from >vigilance_master group by category_id_chv,entry_user_id_int having" >entry_user_id_int=78 >result is : > >ID entry_user_id_int category_id_chv count > >178 VC 1 >278 VE 1 >378 CV 8 >478 SC 1 > >BUT I NEED THE RESULT AS >entry_user_id_int COUNT(VC) COUNT(VE) COUNT(CV) COUNT(SC) TOTAL >781 1 8 1 11 _ Get in the mood for Valentine's Day. View photos, recipes and more on your Live.com page. http://www.live.com/?addTemplate=ValentinesDay&ocid=T001MSN30A0701 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and / or its Customers and is intended for use only by the individual or the entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at [EMAIL PROTECTED] and delete this email from your records. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] hi
Hi I have the data like this in temp table SQL> Select sno, value from temp; SNO Value 1 650.00 2 850.00 3 640.00 3 985.00 5 987.00 9 9864.00 7 875.00 Now, I want display the result like this... SQL> Select sno, value, get_sum(value) as sum from temp; SNO Value SUM 1 650.00 650.00 2 850.00 1500.00 --650+850 3 640.00 2140.00 --1500+640 3 985.00 3125.00 -- 2140+985 5 987.00 4112.00 9 9864.00 13976.00 7 875.00 14851.00 Any one can tell me query for this .. I don't want any procedure or function... Thanks & Regards Penchal Reddy Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and / or its Customers and is intended for use only by the individual or the entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at [EMAIL PROTECTED] and delete this email from your records.
[SQL] Needs Function
PLEASE GO TO FOLLOWING QUERY AND REVERT TO ME IF ANY QUERIES SELECT * FROM TEMP; DEPTNO EMPNO -- -- 10 B3091,B3092,B3093,B3085 11 3651,6521 12 H3062 1 SELECT DEPTNO,SUBSTR(EMPNO,1,5) EMPNO FROM TEMP WHERE DEPTNO=10 2 UNION 3 SELECT DEPTNO,SUBSTR(EMPNO,7,5) EMPNO FROM TEMP WHERE DEPTNO=10 4 UNION 5 SELECT DEPTNO,SUBSTR(EMPNO,13,5) EMPNO FROM TEMP WHERE DEPTNO=10 6 UNION 7 SELECT DEPTNO,SUBSTR(EMPNO,19,5) EMPNO FROM TEMP WHERE DEPTNO=10 8 UNION 9 SELECT DEPTNO,SUBSTR(EMPNO,1,4) EMPNO FROM TEMP WHERE DEPTNO=11 10 UNION 11 SELECT DEPTNO,SUBSTR(EMPNO,6,4) EMPNO FROM TEMP WHERE DEPTNO=11 12 UNION 13* SELECT DEPTNO,EMPNO FROM TEMP WHERE DEPTNO=12 SQL> / DEPTNO EMPNO -- -- 10 B3085 10 B3091 10 B3092 10 B3093 11 3651 11 6521 12 H3062 7 rows selected. Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and / or its Customers and is intended for use only by the individual or the entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at [EMAIL PROTECTED] and delete this email from your records. Disclaimer: This message and the information contained herein is proprietary and confidential and subject to the Tech Mahindra policy statement, you may review at http://www.techmahindra.com/Disclaimer.html externally and http://tim.techmahindra.com/Disclaimer.html internally within Tech Mahindra. Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and / or its Customers and is intended for use only by the individual or the entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at [EMAIL PROTECTED] and delete this email from your records.<>
[SQL] Needs Function
I need a user defined function for the following purpose
If I pass a string with comma ( , ) separated chars/values It should
be appear in next line...
Ex: select get_sep_str ('SK, rp, h, j, 6, 9, kl') from dual;
Output :
SK
rp
h
j
6
9
kl
(Or)
I have one table like this.
Temp Table:
Deptno number(10)
Empno varchar2(200);
Data in temp table:
Deptno Empno
---
10 B3091,B3092,B3093,B3085
11 3651,6521
12 H3062
Now, I want to display the data like this.
Deptno Empno
--
10 B3091
10 B3092
10 B3093
10 B3094
11 3651
11 6521
12 H3062
Now, how can I achieve this
Information transmitted by this e-mail is proprietary to Infinite Computer
Solutions and / or its Customers and is intended for use only by the individual
or the entity to which it is addressed, and may contain information that is
privileged, confidential or exempt from disclosure under applicable law. If you
are not the intended recipient or it appears that this mail has been forwarded
to you without proper authority, you are notified that any use or dissemination
of this information in any manner is strictly prohibited. In such cases, please
notify us immediately at [EMAIL PROTECTED] and delete this email from your
records.<>
[SQL] Needs Query
In a table, a column contains Suneelll Sudhakarr Vivekk Ramana But my output is Suneel Sudhakar Vivek Ramana I want a query to get my output like above Please solve my problem ASAP Regards penchal Disclaimer: This message and the information contained herein is proprietary and confidential and subject to the Tech Mahindra policy statement, you may review at http://www.techmahindra.com/Disclaimer.html externally and http://tim.techmahindra.com/Disclaimer.html internally within Tech Mahindra. Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and / or its Customers and is intended for use only by the individual or the entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at [EMAIL PROTECTED] and delete this email from your records.
[SQL] need help
Hi ... Create table cdano_nya(cdano int4,nyano int4) ... I created this table and then I inserted some values to this( 234576,86)... Now when I am updating this table .. its not updating ..query is continuously running... When I am stopping query its giving this messageERROR: canceling statement due to user request.. May I know the reason y its not running.. and I am unable to drop this table also...when I am selecting this table in pgAdmin..its strucking the pgAdmin. Any one can help in this Thanks & Regards Penchal Reddy Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and / or its Customers and is intended for use only by the individual or the entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at [EMAIL PROTECTED] and delete this email from your records.
[SQL] calculation of pay based on dates
Hi ... I want to fix the pay based on joining date and antedate...antedate is the last date... Joining date is 01-07-2007 Antedate is 04-07-2007.. On joining date I will fix the pay but before fixing the pay I will check whether antedate is there or not, if its there then I will see difference between 2 dates..if difference is 2 years then I will fix the pay with increments...if difference is less than 2 years then I will fix the pay with out increments... Take an example of above 2 dates..there is no 2 years difference between two dates... 01-07-2007 I will fix the pay...after 3 days current date = antedate...so again I have to fix the pay with out my intervention... Is there any jobs or schedules or triggers to call that function when current date and antedate is equal... Any one can help on this... I need it very soon Regards Penchal Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and / or its Customers and is intended for use only by the individual or the entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at [EMAIL PROTECTED] and delete this email from your records. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] hi
I want to fix the pay based on joining date and antedate...antedate is the last date... Joining date is 01-07-2007 Antedate is 04-07-2005.. On joining date I will fix the pay but before fixing the pay I will check whether antedate is there or not, if its there then I will see difference between 2 dates..if difference is 2 years then I will fix the pay with increments...if difference is less than 2 years then I will fix the pay with out increments... Take an example of above 2 dates..there is no 2 years difference between two dates... 01-07-2007 I will fix the pay...after 3 days current date = antedate...so again I have to fix the pay with out my intervention... Is there any jobs or schedules or triggers to call that function when current date and antedate is equal... Thanks & Regards Penchal Reddy Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and / or its Customers and is intended for use only by the individual or the entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at [EMAIL PROTECTED] and delete this email from your records.
Re: [SQL] Returnd Refcurser ( not fetchable
create table foo(sirname text, name text); insert into foo values
('Mueller', 'Marcus');
create function getfoo(char(1)) returns refcursor as '
declare
ref refcursor;
begin
open ref for select * from foo where sirname like ''%'' || $1 ||
''%'';
return next ref;
return
end;
'
LANGUAGE 'plpgsql' VOLATILE;
Copy the above function and execute..i changed at return statement.
Thanks & Regards
Penchal Reddy
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Christian Kindler
Sent: Friday, August 10, 2007 11:28 AM
To: pgsql-sql@postgresql.org
Subject: [SQL] Returnd Refcurser ( not fetchable
Hi!
I have the Problem, that a function returns a refcursor and I am not
able to fetch them. I tried in several ways.
Can you please tell me how to get the cursor data.
Thanks
Chris
PS this is what I have:
create table foo(sirname text, name text);
insert into foo values ('Mueller', 'Marcus');
create function getfoo(char(1)) returns refcursor as
'
declare
ref refcursor;
begin
open ref for select * from foo where sirname like ''%'' || $1 ||
''%'';
return ref;
end;
'
LANGUAGE 'plpgsql' VOLATILE;
select * from getfoo('M');
* fetch all in '';
ERROR: syntax error at or near "''" at character 14
--
cu
Chris
GMX FreeMail: 1 GB Postfach, 5 E-Mail-Adressen, 10 Free SMS.
Alle Infos und kostenlose Anmeldung: http://www.gmx.net/de/go/freemail
---(end of broadcast)---
TIP 6: explain analyze is your friend
Information transmitted by this e-mail is proprietary to Infinite Computer
Solutions and / or its Customers and is intended for use only by the individual
or the entity to which it is addressed, and may contain information that is
privileged, confidential or exempt from disclosure under applicable law. If you
are not the intended recipient or it appears that this mail has been forwarded
to you without proper authority, you are notified that any use or dissemination
of this information in any manner is strictly prohibited. In such cases, please
notify us immediately at [EMAIL PROTECTED] and delete this email from your
records.
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
[SQL] to restore database in schema
Hi ... Under one database who's name is postgres ,its contains 2 schema's 1)cdao1 2)cdao2. I want to restore one database into schema 2.(cdao2)...here there is no option to restore... I can restore into one database,but I cant restore into schema...may I know y its happening... Any one can help me for this Thanks & Regards Penchal Reddy Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and / or its Customers and is intended for use only by the individual or the entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority,you are notified that any use or dissemin- ation of this information in any manner is strictly prohibited.In such cases,please notify us immediately at [EMAIL PROTECTED] and delete this email from your records.
