Re: [SQL] Porting problem from Informix to Postgres...

2004-09-29 Thread Marco Gaiarin 
Mandi! Jeff
  In chel di` si favelave...

> 1. use to_char to format the date however you like

...this seems the best solution, many thanks to all! ;)

-- 
dott. Marco Gaiarin GNUPG Key ID: 240A3D66
  Associazione ``La Nostra Famiglia''http://www.sv.lnf.it/
  Polo FVG  -  Via della Bontà, 7 - 33078  -  San Vito al Tagliamento (PN)
  gaio(at)sv.lnf.it tel +39-0434-842711fax +39-0434-842797

Urbani, dimissioni please
http://punto-informatico.it/p.asp?i=49171

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

[SQL] SELECT with Function

2004-09-29 Thread Paulo Nievierowski 
Hello,

This is my first message to pgsql-novice.

I create plpgsql function "myfunc(integer)" thats returns of type
record with values from table X (the function decides what record must
be select). The parameter is an keyprod existing in table X and in
table A.

It run well sending in psql:
SELECT * FROM myfunc( 10006530 ) as (vret1 numeric, vret2 numeric);

The problem is:
I need return data from table A (this have column 'keyprod'). This
value (keyprod) must be passed as parameter to myfunc(). The result
must be a union of columns selected from table A and result of
myfunc().

How to create this query? It's possible?

Thanks

Paulo Nievierowski

PS: Excuses my poor english.




---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [SQL] FOREIGN KEY and AccessExclusiveLock

2004-09-29 Thread Achilleus Mantzios 

Hmm, (something went wrong with some mailer)

Tom Lane wrote:
> We're adding a trigger to it.

>From the docs:

ACCESS EXCLUSIVE

 Conflicts with locks of all modes (ACCESS SHARE, ROW SHARE, ROW 
EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, 
and ACCESS EXCLUSIVE). This mode guarantees that the holder is the only 
transaction accessing the table in any way. 


Acquired by the ALTER TABLE, DROP TABLE, REINDEX, CLUSTER, and VACUUM FULL 
commands. This is also the default lock mode for LOCK TABLE statements 
that do not specify a mode explicitly. 


Tip: Only an ACCESS EXCLUSIVE lock blocks a SELECT (without FOR UPDATE) 
statement. 


Now, is the lock acquired for the CREATE TRIGGER an explicit
LOCK TABLE?
Because nothing is mentioned about triggers in 
http://www.postgresql.org/docs/7.4/interactive/explicit-locking.html
-- 
-Achilleus


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

[SQL] multiple insert

2004-09-29 Thread van Elsacker Frans 
We developed a program to follow up the time one is working on a 
particular job. They start and stop the time counter different times a 
day, now for already tree years.  Everything works fine but for a short 
while (august?) there are very few actions  from which the beginning 
time is inserted twice, even three times, without special reason. Now 
this occurs a mean of once/two days. We disabled the possibility of 
double clicking on the insert button to avoid double inserting but it 
did not help. The table has appr 40.000 records now.
Employes have same working times and lunch times.( more ticks can have 
same second for start)
vacuum runed.
Can the problem be caused by a heavy loaded server??

The structure of the table is :
CREATE TABLE tikbezig (
   ordernr character(6) NOT NULL,
   lijnnr character(2),
   afdeling character(2),
   werknr character(5),
   begintijd timestamp with time zone,   --startime
   eindtijd timestamp with time zone,   --endtime
   begincomp character varying(20),  -- computername 
startjob
   eindcomp character varying(20),  --computername endjob
   oo integer,
time zone
);

ordernr is not unique but has a index
CREATE INDEX tikbezig_idx ON tikbezig USING btree (ordernr, lijnnr, 
afdeling);

CREATE INDEX tikwerkn_idx ON tikbezig USING btree (werknr);
CREATE CONSTRAINT TRIGGER ""
   AFTER INSERT OR UPDATE ON tikbezig
   FROM werkn
   NOT DEFERRABLE INITIALLY IMMEDIATE
   FOR EACH ROW
   EXECUTE PROCEDURE "RI_FKey_check_ins"('', 'tikbezig', 
'werkn', 'UNSPECIFIED', 'werknr', 'nr');

example
13474 01NA32004-09-24 11:02:502004-09-24 
11:09:43NAAIAFDELINGNAAIAFDELING2004-09-24 11:09:28
13474 01NA32004-09-24 11:02:502004-09-24 
11:09:35NAAIAFDELINGNAAIAFDELING2004-09-24 11:09:31
13474 01NA32004-09-24 11:02:502004-09-24 
11:10:12NAAIAFDELINGNAAIAFDELING2004-09-24 11:09:26
which means same computer, same person, same order on same second.

Any help is welcome
Frans
---(end of broadcast)---
TIP 8: explain analyze is your friend