Re: [SQL] Create trigger for auto update function
CREATE FUNCTION update_pass() RETURNS integer AS $$ UPDATE hoy SET pass_md5=md5(pass) WHERE id=new.id; SELECT 1; $$ LANGUAGE SQL; CREATE TRIGGER triger_users_pass_md5 AFTER INSERT OR UPDATE ON hoy FOR EACH ROW EXECUTE PROCEDURE update_pass; I understand the ideea, but don't know how to apply it. I also receive the error that NEW must be definde as a rule. Still... not working... - Original Message - From: "daq" <[EMAIL PROTECTED]> To: "Andrei Bintintan" <[EMAIL PROTECTED]> Cc: Sent: Monday, July 18, 2005 4:32 PM Subject: Re: [SQL] Create trigger for auto update function Hello Andrei, Monday, July 18, 2005, 2:24:41 PM, you wrote: AB> Hi to all, AB> I have a table: AB> create table hoy( AB> id serial, AB> pass varchar(40), AB> pass_md5 varchar(40); AB> Now, I want to write a trigger function that automatically updates the pass_md5 with the md5 function of the pass. AB> I tried this: AB> CREATE FUNCTION update_pass(integer) RETURNS integer AS $$ AB> UPDATE hoy SET pass_md5=md5(pass) WHERE id=$1; AB>SELECT 1; AB> $$ LANGUAGE SQL; AB> and AB> CREATE TRIGGER triger_users_pass_md5 AB> AFTER INSERT OR UPDATE AB> ON hoy AB> EXECUTE PROCEDURE update_pass(integer); What will be the param of the trigger procedure? Try this way: CREATE FUNCTION update_pass() RETURNS integer AS $$ UPDATE hoy SET pass_md5=md5(pass) WHERE id=new.id; SELECT 1; $$ LANGUAGE SQL; CREATE TRIGGER triger_users_pass_md5 AFTER INSERT OR UPDATE ON hoy FOR EACH ROW EXECUTE PROCEDURE update_pass; DAQ ---(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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Create trigger for auto update function
Hello Andy, Tuesday, July 19, 2005, 9:55:41 AM, you wrote: >> CREATE FUNCTION update_pass() RETURNS integer AS $$ >> UPDATE hoy SET pass_md5=md5(pass) WHERE id=new.id; >>SELECT 1; >> $$ LANGUAGE SQL; >> >> CREATE TRIGGER triger_users_pass_md5 >> AFTER INSERT OR UPDATE >> ON hoy FOR EACH ROW >> EXECUTE PROCEDURE update_pass; A> I understand the ideea, but don't know how to apply it. A> I also receive the error that NEW must be definde as a rule. A> Still... not working... Sorry! My fault. Trigger porcedure returns OPAQUE type. CREATE FUNCTION update_pass() RETURNS OPAQUE AS $$ UPDATE hoy SET pass_md5=md5(pass) WHERE id=new.id; SELECT 1; $$ LANGUAGE SQL; DAQ ---(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
Re: [SQL] Create trigger for auto update function >> SOLVED!!!
CREATE OR REPLACE FUNCTION u9() RETURNS TRIGGER AS' BEGIN NEW.pass_md5=md5(NEW.pass); return NEW; END 'language plpgsql; CREATE TRIGGER t8 BEFORE INSERT OR UPDATE ON hoy FOR EACH ROW EXECUTE PROCEDURE u9(); Ok. This is the solution. It works well, for inserts and updates. Took some time to figure it out. (ignore the function names --- test functions) Best regards, Andy. - Original Message - From: "daq" <[EMAIL PROTECTED]> To: "Andrei Bintintan" <[EMAIL PROTECTED]> Cc: Sent: Monday, July 18, 2005 4:32 PM Subject: Re: [SQL] Create trigger for auto update function Hello Andrei, Monday, July 18, 2005, 2:24:41 PM, you wrote: AB> Hi to all, AB> I have a table: AB> create table hoy( AB> id serial, AB> pass varchar(40), AB> pass_md5 varchar(40); AB> Now, I want to write a trigger function that automatically updates the pass_md5 with the md5 function of the pass. AB> I tried this: AB> CREATE FUNCTION update_pass(integer) RETURNS integer AS $$ AB> UPDATE hoy SET pass_md5=md5(pass) WHERE id=$1; AB>SELECT 1; AB> $$ LANGUAGE SQL; AB> and AB> CREATE TRIGGER triger_users_pass_md5 AB> AFTER INSERT OR UPDATE AB> ON hoy AB> EXECUTE PROCEDURE update_pass(integer); What will be the param of the trigger procedure? Try this way: CREATE FUNCTION update_pass() RETURNS integer AS $$ UPDATE hoy SET pass_md5=md5(pass) WHERE id=new.id; SELECT 1; $$ LANGUAGE SQL; CREATE TRIGGER triger_users_pass_md5 AFTER INSERT OR UPDATE ON hoy FOR EACH ROW EXECUTE PROCEDURE update_pass; DAQ ---(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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Create trigger for auto update function
Andrei Bintintan wrote: Now, I want to write a trigger function that automatically updates the pass_md5 with the md5 function of the pass. I tried this: CREATE FUNCTION update_pass(integer) RETURNS integer AS $$ UPDATE hoy SET pass_md5=md5(pass) WHERE id=$1; SELECT 1; $$ LANGUAGE SQL; and CREATE TRIGGER triger_users_pass_md5 AFTER INSERT OR UPDATE ON hoy EXECUTE PROCEDURE update_pass(integer); The simplest way to do this is with a BEFORE trigger, and just modifying the NEW pseudo-record. CREATE OR REPLACE FUNCTION maintain_pass_md5() RETURNS TRIGGER AS ' BEGIN NEW.pass_md5 = md5(NEW.pass); RETURN NEW; END ' LANGUAGE plpgsql; CREATE TRIGGER hoy_maintain_pass_md5 BEFORE INSERT OR UPDATE ON hoy FOR EACH ROW EXECUTE PROCEDURE maintain_pass_md5(); Note that the function is defined to return type TRIGGER and that we return NEW. If we returned NULL, the row would be skipped by the current update statement. This means only one actual on-disk update takes place, and as far as everyone is concerned pass_md5 automagically updates itself. If the md5() function was actually an operation that would take a long time, it might be worth checking whether pass has been changed: IF NEW.pass IS DISTINCT FROM OLD.pass THEN ... END IF However, if you do this then you have to test TG_OP to see whether you are inserting or updating - insert ops don't have OLD defined. HTH -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Create trigger for auto update function
Off topic :) I think we posted in the same time :)) - Original Message - From: "Richard Huxton" To: "Andrei Bintintan" <[EMAIL PROTECTED]> Cc: Sent: Tuesday, July 19, 2005 12:11 PM Subject: Re: [SQL] Create trigger for auto update function Andrei Bintintan wrote: Now, I want to write a trigger function that automatically updates the pass_md5 with the md5 function of the pass. I tried this: CREATE FUNCTION update_pass(integer) RETURNS integer AS $$ UPDATE hoy SET pass_md5=md5(pass) WHERE id=$1; SELECT 1; $$ LANGUAGE SQL; and CREATE TRIGGER triger_users_pass_md5 AFTER INSERT OR UPDATE ON hoy EXECUTE PROCEDURE update_pass(integer); The simplest way to do this is with a BEFORE trigger, and just modifying the NEW pseudo-record. CREATE OR REPLACE FUNCTION maintain_pass_md5() RETURNS TRIGGER AS ' BEGIN NEW.pass_md5 = md5(NEW.pass); RETURN NEW; END ' LANGUAGE plpgsql; CREATE TRIGGER hoy_maintain_pass_md5 BEFORE INSERT OR UPDATE ON hoy FOR EACH ROW EXECUTE PROCEDURE maintain_pass_md5(); Note that the function is defined to return type TRIGGER and that we return NEW. If we returned NULL, the row would be skipped by the current update statement. This means only one actual on-disk update takes place, and as far as everyone is concerned pass_md5 automagically updates itself. If the md5() function was actually an operation that would take a long time, it might be worth checking whether pass has been changed: IF NEW.pass IS DISTINCT FROM OLD.pass THEN ... END IF However, if you do this then you have to test TG_OP to see whether you are inserting or updating - insert ops don't have OLD defined. HTH -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Postgres for Fedora Core 2 OS ****************
There is the postgresql-8.0.3-1.i386.rpm for Fedora Core 4 in http://ftp.idilis.ro/mirrors/fedora/core/4/i386/os/Fedora/RPMS/ and others mirrors. I don't know if it works with Fedora Core 2. Probably not. For Fedora Core 3 there is the postgresql-7.4.6-1.FC3.1.i386.rpm in http://ftp.idilis.ro/mirrors/fedora/core/3/i386/os/Fedora/RPMS/ and for Fedora Core 2 there is the postgresql-7.4.2-1.i386.rpm in http://ftp.idilis.ro/mirrors/fedora/core/2/i386/os/Fedora/RPMS/ Can't you upgrade to Fedora Core 4? I will. Regards, Halley __ Converse com seus amigos em tempo real com o Yahoo! Messenger http://br.download.yahoo.com/messenger/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] echo/printf function in plpgsql
Hi all! Is there a way of echo'ing a string(like "raise notice 'this is id%', id") from plpgsql? I want to echo/print it to STDOUT 'cause the notice-mechanism produces too much noise IMH. -- Andreas ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] echo/printf function in plpgsql
Andreas Joseph Krogh wrote: Hi all! Is there a way of echo'ing a string(like "raise notice 'this is id%', id") from plpgsql? I want to echo/print it to STDOUT 'cause the notice-mechanism produces too much noise IMH. Your function is running in the backend. You don't have a STDOUT (although you might have redirected STDERR for logging). -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] echo/printf function in plpgsql
On Tuesday 19 July 2005 17:18, Richard Huxton wrote: > Andreas Joseph Krogh wrote: > > Hi all! > > > > Is there a way of echo'ing a string(like "raise notice 'this is id%', > > id") from plpgsql? I want to echo/print it to STDOUT 'cause the > > notice-mechanism produces too much noise IMH. > > Your function is running in the backend. You don't have a STDOUT > (although you might have redirected STDERR for logging). I see. Can I make the ouput somehow less verbose? It spits out a lot of noise for each "NOTICE": psql:ocs_process_projecct.sql:48: NOTICE: trying to insert parent_id: 87, child_id: 91 CONTEXT: SQL statement "SELECT processSubProject( $1 , $2 , $3 )" PL/pgSQL function "processsubproject" line 7 at perform SQL statement "SELECT processSubProject( $1 , $2 , $3 )" PL/pgSQL function "processsubproject" line 7 at perform SQL statement "SELECT processSubProject( $1 , $2 , $3 )" PL/pgSQL function "build_project_children" line 11 at perform -- AJk ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] funstions for parsing words
I have a table called Phrases that holds the text of a phrase. I want write a query that will return all the words found in all the text of the Phrases. Like so: Phrases: "Hello World" "Goodbye World" "I like candy Words (select statement result): "Hello" "World" "Goodbye" "I" "Like" "Candy" Is anything like this possible? Thanks alot. Your Friend, John Kopanas ---(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
Re: [SQL] funstions for parsing words
On 7/19/05, John Kopanas <[EMAIL PROTECTED]> wrote: > I have a table called Phrases that holds the text of a phrase. I want > write a query that will return all the words found in all the text of > the Phrases. Like so: > > > Phrases: > > "Hello World" > "Goodbye World" > "I like candy > > Words (select statement result): > > "Hello" > "World" > "Goodbye" > "I" > "Like" > "Candy" > > Is anything like this possible? > > Thanks alot. > > Your Friend, > > John Kopanas You can do this by using array_to_string and using a space as your delimiter. If you need to trim the quotes use the trim function also. You can also see the split_on_commas example below -- you'd want to split on a space. I would also rewrite this to use array_to_string or use pl/perl if you can. http://archives.postgresql.org/pgsql-sql/2005-05/msg00204.php Hope this helps. Tony Wasson ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] echo/printf function in plpgsql
On 7/19/05, Andreas Joseph Krogh <[EMAIL PROTECTED]> wrote: > On Tuesday 19 July 2005 17:18, Richard Huxton wrote: > > Andreas Joseph Krogh wrote: > > > Hi all! > > > > > > Is there a way of echo'ing a string(like "raise notice 'this is id%', > > > id") from plpgsql? I want to echo/print it to STDOUT 'cause the > > > notice-mechanism produces too much noise IMH. > > > > Your function is running in the backend. You don't have a STDOUT > > (although you might have redirected STDERR for logging). > > I see. Can I make the ouput somehow less verbose? It spits out a lot of noise > for each "NOTICE": You can control the severity messages sent to your client by first setting client_min_message. Try SET client_min_messages = WARNING; http://www.postgresql.org/docs/8.0/interactive/runtime-config.html Tony ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] echo/printf function in plpgsql
On Tuesday 19 July 2005 22:09, Tony Wasson wrote: > On 7/19/05, Andreas Joseph Krogh <[EMAIL PROTECTED]> wrote: > > On Tuesday 19 July 2005 17:18, Richard Huxton wrote: > > > Andreas Joseph Krogh wrote: > > > > Hi all! > > > > > > > > Is there a way of echo'ing a string(like "raise notice 'this is id%', > > > > id") from plpgsql? I want to echo/print it to STDOUT 'cause the > > > > notice-mechanism produces too much noise IMH. > > > > > > Your function is running in the backend. You don't have a STDOUT > > > (although you might have redirected STDERR for logging). > > > > I see. Can I make the ouput somehow less verbose? It spits out a lot of > > noise for each "NOTICE": > > You can control the severity messages sent to your client by first > setting client_min_message. > > Try SET client_min_messages = WARNING; Thanks -- AJK ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] Coalesce() in outer join between views
Hi everyone.
I am trying to create a view that fills in missing values from a
secondary source. I am using PostgreSQL 8.0.3 on Fedora Linux Core 3.
I have two important views and two important tables. Everything works
find by itself but when I try to create an outer join between views
(that hit the same table) coalesce is giving bad results.
The first view is day_source_pre:
View "reporting.day_source_pre"
Column | Type | Modifiers
+--+---
day| date |
amount | double precision |
source | text |
View definition:
( SELECT acc_trans.transdate AS "day", sum(acc_trans.amount) AS amount,
payment_types.id AS source
FROM acc_trans, payment_types
WHERE (acc_trans.chart_id IN ( SELECT chart.id
FROM chart
WHERE chart.accno ~~ '1300.%'::text)) AND acc_trans.source ~~
(('%source='::text || payment_types.id) || '%'::text)
GROUP BY acc_trans.transdate, payment_types.id
UNION
SELECT acc_trans.transdate AS "day", sum(acc_trans.amount) AS amount,
'over/under' AS source
FROM acc_trans
WHERE (acc_trans.chart_id IN ( SELECT chart.id
FROM chart
WHERE chart.accno ~~ '1300.%'::text)) AND acc_trans.source ~~
'%Over/under%'::text
GROUP BY acc_trans.transdate)
UNION
SELECT acc_trans.transdate AS "day", sum(acc_trans.amount) AS amount,
'Reset' AS source
FROM acc_trans
WHERE (acc_trans.chart_id IN ( SELECT chart.id
FROM chart
WHERE chart.accno ~~ '1300.%'::text)) AND acc_trans.source ~~
'%Reset%'::text
GROUP BY acc_trans.transdate;
This works as expected by itself.
The second view is:
View "reporting.day_inc_source"
Column | Type | Modifiers
+--+---
day| date |
sum| double precision |
source | text |
View definition:
SELECT acc_trans.transdate AS "day", sum(acc_trans.amount) AS sum,
acc_trans.source
FROM acc_trans
WHERE acc_trans.source IS NOT NULL
GROUP BY acc_trans.transdate, acc_trans.source;
This works OK by itself.
The third view (which is where tthe problem is) is defined thuswise:
View "reporting.day_source"
Column | Type | Modifiers
+--+---
day| date |
source | text |
amount | double precision |
View definition:
SELECT day_inc_source."day", day_inc_source.source,
COALESCE(day_source_pre.amount, day_inc_source.sum * -1::double
precision) AS amount
FROM reporting.day_source_pre
RIGHT JOIN reporting.day_inc_source ON day_source_pre.amount =
day_inc_source.sum AND day_source_pre."day" = day_inc_source."day"
WHERE (day_inc_source.source IN ( SELECT payment_types.id
FROM payment_types))
ORDER BY day_inc_source."day";
The problem seems to be somehow assuming that all amount columns in
day_source_pre are null. Is there something wrong in how this view is
working, or is it (more likely) my SQL syntax?
That I want to do is fill in a value from day_inc_source if and only if
it is not found in day_source_pre with the same date and amount.
Best Wishes,
Chris Travers
Metatron Technology Consulting
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] difference between all RDBMSs
Obviously on this list you will mostly get info on PostgreSQL. With regard to PostgreSQL, I would highly suggest familiarizing yourself with the online documentation. I won't cover the weaknesses of MySQL here, but will give you a quick overview on how PostgreSQL is different from other RDBMS's so you can refine your search a bit. PostgreSQL is designed to be extremely extensible. This means that one can easily write code to add data types, procedural languages, and more with very little work. Other database managers may allow for data types to be added, but I am not aware of any others that allow you to define your own procedural langauges in any arbitrary way (Even the recent enhancements to MS SQL to give it access to .Net are not this advanced). The PostgreSQL development team has made data integrity and stability (assuming working hardware) a top priority. It is certainly a higher priority than any other open source RDBMS I have ever worked with. If you want to understand other factors that make PostgreSQL different than other RDBMS's you may want to look into differences regarding: ISO compliance (which features of SQL-99 are supported), the trigger vs. rule systems in PostgreSQL (warning MS SQL uses something they call rules but it is something different), and features like inherited tables. Again, read the online documentation. Best Wishes, Chris Travers Metatron Technology Consulting ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] echo/printf function in plpgsql
On Jul 19, 2005, at 11:58 AM, Andreas Joseph Krogh wrote: I see. Can I make the ouput somehow less verbose? It spits out a lot of noise for each "NOTICE": If you just want to output some information to the log, you can use something like this: raise log 't is %', t; If I recall correctly, the values to be inserted into the format string can only be variables, not expressions. John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(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
