[SQL] Importation wtih copy generated some wrong registers..
Hi everyone, I need some help please I did some importation from a .csv file to a table (this is a very small piece of the csv file, but i think its good for the exemple. the pipe "|" is the delimiter for this file) 001|002|3041300045027612|002 001|002|3041000218146611|002 001|002|3040600664389616|002 001|002|3040600607799814|002 001|002|3040600664429313|002 001|002|3040600607767316|002 (and the respective part of the table is) cod_empresa int4 NOT NULL, cod_credor int4 NOT NULL, num_contr varchar(50) NOT NULL, cod_filial int4 NOT NULL DEFAULT 1 (so, the data on my tables look like this:) cod_empresa | cod_cliente | num_contr | cod_filial -+-+---+ 1 |9386 | 3041000762732212 | 3 1 |7383 | 3,0410007634E+019 | 4 1 |5750 | 3041000763546413 | 3 1 |3584 | 3,0410007627E+019 | 6 altough the data on the 'num_contr' column is not exatly the same in the csv file described above, it ilustrates how the data is being stored on my table. So my question is, why is it storing some exponential numbers (rows number 2 and 4) instead of a string if you look in the definition of the table above, the column 'num_contr' is a varchar(50)! So, what is happening? Thanks for any help!!! = Rodrigo Sakai Analista Programador [EMAIL PROTECTED] http://www.2bfree.com.br Tel: (55) (11) 5083-5577 Fax: (55) (11) 5549-3598 = ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Compiling pl/pgsql functions
Hi, I'm responsable for the database here in the company, and I like to know if is there a way to compile my pl/pgsql functions, its not a performance problem, it is more a security problem, i don like to have somebody looking into my codes and see the company rules. Is there a way to do that, or the only way is writting my functions in C?? Thanks for any help and regards to all!!! = Rodrigo Sakai Database Programmer [EMAIL PROTECTED] http://www.2bfree.com.br Tel: (55) (11) 5083-5577 Fax: (55) (11) 5549-3598 = ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Compiling pl/pgsql functions
>AFAIK there's not much you can do for obfuscation of pl functions right >now since someone will be able to see the src text in pg_proc. However, >are you allowing people that you don't want to see the code access to >write arbitrary sql to the database? Let me explain myself a little better. Actualy we sell software, and some codes of the systems we develope here are inside the database as functions, so we can compile the codes of the system (php, java, etc...), but not the codes that are in the postgresql. Some of our clientes, need that a employee of them get total access to the database instaled locally, becoming the database administrator. Thats ok, but to protect our postgresql codes (functions) i like to compile my plpgsql functions, so our client's DBA will be able to do anything he wants with the database, but will not be able to get our codes. I insist in my question, is there a way to compile the plpgsql codes or something like that, or its better to think about writting this postgres functions in C?? Thanks for all!!! ===== Rodrigo Sakai Database Programmer [EMAIL PROTECTED] http://www.2bfree.com.br Tel: (55) (11) 5083-5577 Fax: (55) (11) 5549-3598 = ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] How to avoid (stop) a endless loop in a trigger
Hi people, i have a problem here. I'm doing a trigger that when a update occurs i need to do an update on the same table (target table), but as known, it causes a endless loop whithin infinit updates. So I need to stop the trigger after it does the first update, is there any way? I tried to do a return null, but that was a very bad idea because it stops completly the function fired by a trigger and all its computation is in vain... The test trigger that i did is like : CREATE OR REPLACE FUNCTION public.sp_teste_loop() RETURNS trigger AS ' begin raise notice \'Trigger Fired\'; if (TG_OP = \'INSERT\') then update teste_trigger set flg_bool = \'S\' where codigo=NEW.codigo; RETURN NEW; elsif (TG_OP = \'UPDATE\') then update teste_trigger set flg_bool = \'N\' where codigo=NEW.codigo; RETURN NULL; end if; end; ' LANGUAGE 'plpgsql' VOLATILE; CREATE TRIGGER tr_sp_teste_trigger BEFORE INSERT OR UPDATE ON public.teste_trigger FOR EACH ROW EXECUTE PROCEDURE public.sp_teste_loop(); Thank for any help and regards = Rodrigo Sakai Database Programmer [EMAIL PROTECTED] http://www.2bfree.com.br Tel: (55) (11) 5083-5577 Fax: (55) (11) 5549-3598 = ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Joins between int and int[]
Hi, I want to use the system catalog tables/views to query some things, and one of the queries I have to do is joining pg_attribute and pg_constraint to know what constraint belongs to which table.attribute. My question is how can I write the join clause between a int (pg_attribute.attnum) and int[ ] (pg_constraint.conkey). The query is: select relname, attname, attnotnull, atthasdef from pg_class as pc inner join pg_attribute as pa on pc.oid=pa.attrelid inner join pg_constraint pcons on pc.oid=pcons.conrelid and pa.attnum = pcons.conkey -à the problem Thanks!!!
RES: [SQL] Joins between int and int[]
Thanks Mantzios, your answer helped a lot! But I have a lot of multi column foreign keys! Any other ideia?? Thanks in advance! -Mensagem original- De: Achilleus Mantzios [mailto:[EMAIL PROTECTED] Enviada em: quarta-feira, 28 de junho de 2006 04:01 Para: Rodrigo Sakai Assunto: Re: [SQL] Joins between int and int[] O Rodrigo Sakai Ýãñáøå óôéò Jun 27, 2006 : > Hi, > > > > I want to use the system catalog tables/views to query some things, and > one of the queries I have to do is joining pg_attribute and pg_constraint to > know what constraint belongs to which table.attribute. My question is how > can I write the join clause between a int (pg_attribute.attnum) and int[ ] > (pg_constraint.conkey). Are you having tables with multi column foreign keys? e.g. (a,b) REFERENCES partable(para,parb). If not then join with pg_constraint.conkey[1], if yes then the problem becomes a little less trivial. > > > > > > The query is: > > > > select relname, attname, attnotnull, atthasdef > > from pg_class as pc > > inner join pg_attribute as pa > > on pc.oid=pa.attrelid > > inner join pg_constraint pcons > > on pc.oid=pcons.conrelid > > and pa.attnum = pcons.conkey ---> the problem > > > > > > Thanks!!! > > > > -- -Achilleus ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] Null attributes
How can I get all attributes that is not null + don’t have defaults + is not PKs??? I know I have to use system catalog, but I have some Pks that is multicolumn! Any help?? Rodrigo Sakai Administrador de Banco de Dados É(11) 3750-7075 * [EMAIL PROTECTED]
[SQL] SEQUENCES
Hi all, I need to get all sequences and their respective current values! Is there any catalog table or any other away to get this??? Thanks in advance.
[SQL] Inserting data in composite types!
Hi, I have a question about how to insert data in composite types! Imagine the exemple: CREATE TYPE t_time AS ( a date, b date ); CREATE TABLE salary ( salary numeric(10,2), t_date t_time ); I know that if I want to insert data in the table SALARY I just have to do like: INSERT INTO salary VALUES (1000.00, ‘(2006/10/10, 2006/12/10)’); But if I have another table: CREATE TABLE employee ( employee_id int, name varchar(30), emp_salary salary ) How can I insert a single row in this table??? Thanks in advamce!
RES: [SQL] Inserting data in composite types!
Thanks, It works! I have tried: insert into employee (c, nome, sal) values(1, 'emp name', '(1, (01/01/2000, 01/01/2000))' ); And it doesn't work! -Mensagem original- De: imad [mailto:[EMAIL PROTECTED] Enviada em: segunda-feira, 13 de novembro de 2006 11:10 Para: [EMAIL PROTECTED] Cc: pgsql-sql@postgresql.org Assunto: Re: [SQL] Inserting data in composite types! I am able to do this thing with the following query: insert into employee (a, name, s) values(1, 'emp name', ((1, '(01/01/2000, 01/01/2000)'))); --Imad www.EnterpriseDB.com On 11/13/06, Rodrigo Sakai <[EMAIL PROTECTED]> wrote: > > > > > Hi, I have a question about how to insert data in composite types! > > > > Imagine the exemple: > > > > CREATE TYPE t_time AS ( > > a date, > > b date > > ); > > > > CREATE TABLE salary ( > >salary numeric(10,2), > >t_date t_time > > ); > > > > I know that if I want to insert data in the table SALARY I just have to do > like: > > > > INSERT INTO salary VALUES (1000.00, '(2006/10/10, 2006/12/10)'); > > > > But if I have another table: > > > > CREATE TABLE employee ( > > employee_id int, > > name varchar(30), > > emp_salary salary > > ) > > > > How can I insert a single row in this table??? > > > > Thanks in advamce! > > ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] Another question about composite types
Hi, I need to create a table like: CREATE TYPE t_salary AS ( Value numeric(10,2), Validity date ); CREATE TABLE employee ( id int, name varchar(30), salary t_salary[] ); That is, I need an array of composite type and searching on google I found that is impossible to do it! Is it really impossible? Anyone have do it ? Thanks!
RES: [SQL] Another question about composite types
I know that if I create a table salary that references my employee table
works! But I'm trying to work with the composite type concept!
Thinking in Object-Oriented Programming (OOP) I have a class employee that
is composed by the class salary among other things! Like:
Class salary {
private float value;
private date validity;
}
Class employee {
private int ID;
private String name;
private salary[] sal;
}
So I'd like to map these classes to PostgreSQL. I think nested tables are
one of the advantages of object-relational databases. And it works if I
think in just one salary for each employee. But I want to keep all salary
history for each employee. Is there any way?
-Mensagem original-
De: Richard Broersma Jr [mailto:[EMAIL PROTECTED]
Enviada em: segunda-feira, 13 de novembro de 2006 14:46
Para: [EMAIL PROTECTED]; pgsql-sql@postgresql.org
Assunto: Re: [SQL] Another question about composite types
> CREATE TYPE t_salary AS (Value numeric(10,2), Validity date);
> CREATE TABLE employee (id int, name varchar(30), salary t_salary[]);
>
> That is, I need an array of composite type and searching on google I found
> that is impossible to do it! Is it really impossible? Anyone have do it ?
Why not use a conventional table to hold this information that references
your employee table?
Regards,
Richard Broersma Jr.
---(end of broadcast)---
TIP 6: explain analyze is your friend
