[SQL] Issue with plpgsql trigger
I'm only learning this, but I've got a working function that I want to be invoked in a trigger. The function itself is: CREATE FUNCTION online_pct_func(integer, interval) RETURNS boolean AS ' DECLARE on numeric; off numeric; o_pct numeric; op varchar; BEGIN on := 0; off := 0; SELECT count(info.online) INTO on FROM info WHERE info.id = $1 AND info.online = ''1'' AND info.iso_date_time > (now() - CAST($2 AS interval)); SELECT count(info.online) INTO off FROM info WHERE info.id = $1 AND info.online = ''0'' AND info.iso_date_time > (now() - CAST($2 AS interval)); o_pct := (on / (on + off)) * 100.0; op := ''UPDATE online_pct ''; IF $2 = ''01:00'' THEN op := op || ''SET on1hr = '' || o_pct || '', isodt1hr = current_timestamp ''; ELSIF $2 = ''1 day'' THEN op := op || ''SET on1day = '' || o_pct || '', isodt1day = current_timestamp ''; ELSIF $2 = ''1 week'' THEN op := op || ''SET on1wk = '' || o_pct || '', isodt1wk = current_timestamp ''; ELSIF $2 = ''1 mon'' THEN op := op || ''SET on1mo = '' || o_pct || '', isodt1mo = current_timestamp ''; ELSIF $2 = ''1 year'' THEN op := op || ''SET on1yr = '' || o_pct || '', isodt1yr = current_timestamp ''; END IF; op := op || ''WHERE id = '' || $1; EXECUTE op; RETURN 1; END; ' LANGUAGE 'plpgsql'; It gives the intended outcome by itself, for example with select online_pct_func(1, '1 year'); The trigger I've got, which is not working for me, is as follows: CREATE FUNCTION online_pct_trig() RETURNS opaque AS ' DECLARE i1hrtimestamp; i1day timestamp; i1wktimestamp; i1motimestamp; i1yrtimestamp; opervarchar; BEGIN SELECT o.isodt1hr INTO i1hr, o.isodt1day INTO i1day, o.isodt1wk INTO i1wk, o.isodt1mo INTO i1mo, o.isodt1yr INTO i1yr FROM online_pct o WHERE o.id = NEW.id; oper := ''SELECT online_pct_func(NEW.id, ''; IF i1hr <= (now() - ''00:01''::interval) THEN oper := oper || ''01:00'' || '');''; EXECUTE oper; END IF; IF i1day < (now() - ''01:00''::interval) THEN oper := oper || ''1 day'' || '');''; EXECUTE oper; END IF; IF i1wk < (now() = ''1 day''::interval) THEN oper := oper || ''1 week'' || '');''; EXECUTE oper; END IF; IF i1mo < (now() - ''1 week''::interval) THEN oper := oper || ''1 mon'' || '');''; EXECUTE oper; END IF; IF il1yr < (now() - ''1 mon''::interval) THEN oper := oper || ''1 year'' || '');''; EXECUTE oper; END IF; RETURN NEW; END; ' LANGUAGE 'plpgsql'; CREATE TRIGGER online_pct_trig AFTER INSERT ON info FOR EACH ROW EXECUTE PROCEDURE online_pct_trig(); I'm guessing there's an obvious error in there that I can't identify; but I've gotten nowhere with it on my own. I guess I'm looking for any input on this.. - Oeln ---(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] next integer in serial key
The same way the default value is defined, which you can find by doing:
\d tablename
Which usually gives something like:
Table "public.gbs_floorplans"
Column| Type |Modifiers
--+---+-
-
floorplan_id | integer | not null default
nextval('public.gbs_floorplans_floorplan_id_seq'::text)
division_id | character(3) | not null
floorplan_display_id | character(10) | not null
Hence
SELECT nextval('public.gbs_floorplans_floorplan_id_seq'::text)
Terry Fielder
Manager Software Development and Deployment
Great Gulf Homes / Ashton Woods Homes
[EMAIL PROTECTED]
Fax: (416) 441-9085
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] Behalf Of Kenneth Gonsalves
> Sent: Wednesday, July 21, 2004 10:46 PM
> To: [EMAIL PROTECTED]
> Subject: [SQL] next integer in serial key
>
>
> hi,
> how does one get the next number in a serial type row?
> --
> regards
> kg
>
> http://www.onlineindianhotels.net - hotel bookings
> reservations in over 4600
> hotels in India
> http://www.ootygolfclub.org
>
> ---(end of
> broadcast)---
> TIP 7: don't forget to increase your free space map settings
>
---(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] Issue with plpgsql trigger
Only wanted to indicate further that I know that IF loop is bad logic in itself, where oper will get overwritten in each case. I've now got the following instead: CREATE FUNCTION online_pct_trig() RETURNS opaque AS ' DECLARE i1hrtimestamp; i1day timestamp; i1wktimestamp; i1motimestamp; i1yrtimestamp; opervarchar; BEGIN SELECT o.isodt1hr INTO i1hr, o.isodt1day INTO i1day, o.isodt1wk INTO i1wk, o.isodt1mo INTO i1mo, o.isodt1yr INTO i1yr FROM online_pct o WHERE o.id = NEW.id; IF i1hr <= (now() - ''00:01''::interval) THEN oper := ''SELECT online_pct_func(NEW.id, ''; oper := oper || ''01:00'' || '');''; EXECUTE oper; END IF; IF i1day < (now() - ''01:00''::interval) THEN oper := ''SELECT online_pct_func(NEW.id, ''; oper := oper || ''1 day'' || '');''; EXECUTE oper; END IF; IF i1wk < (now() = ''1 day''::interval) THEN oper := ''SELECT online_pct_func(NEW.id, ''; oper := oper || ''1 week'' || '');''; EXECUTE oper; END IF; IF i1mo < (now() - ''1 week''::interval) THEN oper := ''SELECT online_pct_func(NEW.id, ''; oper := oper || ''1 mon'' || '');''; EXECUTE oper; END IF; IF i1yr < (now() - ''1 mon''::interval) THEN oper := ''SELECT online_pct_func(NEW.id, ''; oper := oper || ''1 year'' || '');''; EXECUTE oper; END IF; RETURN NEW; END; ' LANGUAGE 'plpgsql'; CREATE TRIGGER online_pct_trig AFTER INSERT ON info FOR EACH ROW EXECUTE PROCEDURE online_pct_trig(); I get no errors when I create it; but it isn't invoking the function either - that's what the issue is, but I'm not certain why or what I've got to correct in order to get it to work. Thank for any input.. - Oeln ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] PSQL Syntax errors running PL/SQL scripts
Hi,
I am trying to run SQL scripts creating schema in PSQL [PostgreSQL
7.4.3]
and getting tons of errors, mostly "syntax error at or near
character..."
Complains about CONSTRAINTS, Prompt, Set, Local, CREATE, '(', ')' ,...
Also many errors "relation ... already exists"
[I guess, if DROP failed]
My script runs fine with Oracle 8i/9i.
Is PostgreSQL so much different from PL/SQL ?
Any good syntax reference for PostgreSQL ?
Thank you in advance,
Oleg.
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] C++ interface problem with libpq.so.3
Tom Lane wrote: Kemin Zhou <[EMAIL PROTECTED]> writes: I have writte a simple program to access PGDB from a C++ program using the libpq++-4.0 Most of time it runs perfect. This morning, it was working fine. Then later it stopped working. The symptom is that my program went to asleep. Looks to me like it's just waiting for a response from the backend. I'd suggest looking into what the backend is doing. I doubt you have an "interface" problem at all... regards, tom lane I figured out my problem. I gave a BEGIN; command and forgot to type COMMIT or ROLLBACK. The access to this table is blocked. So my front end went to sleep (waiting for ever). Hope this may be useful to other people. Kemin ** Proprietary or confidential information belonging to Ferring Holding SA or to one of its affiliated companies may be contained in the message. If you are not the addressee indicated in this message (or responsible for the delivery of the message to such person), please do not copy or deliver this message to anyone. In such case, please destroy this message and notify the sender by reply e-mail. Please advise the sender immediately if you or your employer do not consent to e-mail for messages of this kind. Opinions, conclusions and other information in this message represent the opinion of the sender and do not necessarily represent or reflect the views and opinions of Ferring. ** ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Table and/or Database Creation Time
Albretch wrote: Is there such thing as a table or database creation time in the SQL standard, that you could avail yourself of? I mean do databases keep this info. I think they do since they are like little OSs and many of them have internal back up features, that must use some kind of timing. Or? ---(end of broadcast)--- TIP 8: explain analyze is your friend Regarding this. I am just recently thinking about this question. I looked into the Postgres Internals Chapter and did not find anything for recording the Table Creation, Last Update, Last access (This one may be too expensive to store). But the Last Update (Date) is important in some cases. Here I am developing a web application that depends on a database (postgres). To increate the response time I cache the query result locally and stored into a html file. Because the data is not update that frequently, this can help. But I don't think Postgres has this information available. I don't see it is difficult to add this information to the database. The question is the overhead; does it worth to keep this information? However, we can easily create a table create table ( table_name text, last_update date ); And write a few triggers (better rules) to monitor a few important tables of interest. I would like to hear from others. Kemin ** Proprietary or confidential information belonging to Ferring Holding SA or to one of its affiliated companies may be contained in the message. If you are not the addressee indicated in this message (or responsible for the delivery of the message to such person), please do not copy or deliver this message to anyone. In such case, please destroy this message and notify the sender by reply e-mail. Please advise the sender immediately if you or your employer do not consent to e-mail for messages of this kind. Opinions, conclusions and other information in this message represent the opinion of the sender and do not necessarily represent or reflect the views and opinions of Ferring. ** ---(end of broadcast)--- TIP 8: explain analyze is your friend
