Re: [SQL] How to get localized to_char(DATE) output
On 2010-05-06, Thomas Kellerer wrote: > Tom Lane, 06.05.2010 00:51: >> Thomas Kellerer writes: >>> I'm trying to get the output of the to_char(date, text) method in German >>> but I can't get it to work: >> >> I think you need 'TMMon' to get a localized month name. >> >> regards, tom lane >> > Ah! Silly me. Now that you write this I can see it in the manual ;) > > The manual says the value for lc_time is OS dependent and indeed "set lc_time > = 'German'" does not work on Solaris. > > Is there a way to get a list of allowed values for lc_time for a specific > installation? "man -k locale" would be my starting point (for anything POSIXish) Looks like "locale -a" does it on linux, that may be worth a try. "de_DE" is the locale for German as used in Germany. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] How to get localized to_char(DATE) output
Jasen Betts, 06.05.2010 11:57: The manual says the value for lc_time is OS dependent and indeed "set lc_time = 'German'" does not work on Solaris. Is there a way to get a list of allowed values for lc_time for a specific installation? "man -k locale" would be my starting point (for anything POSIXish) Looks like "locale -a" does it on linux, that may be worth a try. "de_DE" is the locale for German as used in Germany. Thanks for the answer. Is there a way to get this information from within a SQL statement? Regards Thomas -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] How to get localized to_char(DATE) output
Thomas Kellerer writes: > Jasen Betts, 06.05.2010 11:57: >>> Is there a way to get a list of allowed values for lc_time for a specific >>> installation? >> Looks like "locale -a" does it on linux, that may be worth a try. > Thanks for the answer. Is there a way to get this information from within a > SQL statement? No. Postgres doesn't know anything about that. "locale -a" should work on pretty much any Unix-ish system, but I dunno what the equivalent on Windows would be. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] How to get localized to_char(DATE) output
Tom Lane, 06.05.2010 16:22: Looks like "locale -a" does it on linux, that may be worth a try. Thanks for the answer. Is there a way to get this information from within a SQL statement? No. Postgres doesn't know anything about that. "locale -a" should work on pretty much any Unix-ish system, but I dunno what the equivalent on Windows would be. OK, thanks a lot Regards Thomas -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Need a help in regexp
Hi,
Need a help in regexp!
I have a table in which the data's are entered like,
Example:
One (1)
Two (2)
Three (3)
I want to extract the data which is only within the parentheses.
that is
1
2
3
i have written a query,
*select regexp_matches(name,'([^(]+)([)]+)','g') from table;*
which outputs the data as,
{"test"}
{"test2"}
Thank You
Nicholas I
Re: [SQL] Need a help in regexp
In response to Nicholas I :
> Hi,
>
> Need a help in regexp!
>
> I have a table in which the data's are entered like,
>
> Example:
>
> One (1)
> Two (2)
> Three (3)
>
> I want to extract the data which is only within the parentheses.
>
> that is
> 1
> 2
> 3
>
> i have written a query,
> select regexp_matches(name,'([^(]+)([)]+)','g') from table;
> which outputs the data as,
> {"test"}
> {"test2"}
>
>
> Thank You
> Nicholas I
test=*# select * from regex ;
t
---
one (1)
two (2)
three (3)
(3 Zeilen)
Zeit: 0,262 ms
test=*# select regexp_replace(t, '[^0-9]','','g') from regex;
regexp_replace
1
2
3
(3 Zeilen)
Regards, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Need a help in regexp
On Thursday 6. May 2010 16.48.26 Nicholas I wrote:
> Hi,
>
> Need a help in regexp!
>
> I have a table in which the data's are entered like,
>
> Example:
>
> One (1)
> Two (2)
> Three (3)
>
> I want to extract the data which is only within the parentheses.
>
> that is
> 1
> 2
> 3
>
> i have written a query,
> *select regexp_matches(name,'([^(]+)([)]+)','g') from table;*
> which outputs the data as,
> {"test"}
> {"test2"}
If what you've got inside the parentheses always is an integer, and it's
always the only or first integer in the string, you can use:
SELECT (REGEXP_MATCHES(bar, E'(\\d+)'))[1] FROM foo;
You can even cast it to an integer on the fly:
SELECT (REGEXP_MATCHES(bar, E'(\\d+)'))[1]::INTEGER FROM foo;
Or as a more general case, whatever's inside (the first) set of parentheses:
SELECT (REGEXP_MATCHES(bar, E'\\((.+?)\\)'))[1] FROM foo;
regards,
--
Leif Biberg Kristensen
http://solumslekt.org/blog/
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Column Specific Update Trigger Routine
I am trying to create a update trigger on a table that basically will only fire when a specific column is updated. I am using version 8.4.3. My plan of attack was to always fire on any row update, and pass in the OLD and NEW column that I want to check. CREATE TRIGGER check_lockout AFTER UPDATE ON acct_table FOR EACH ROW EXECUTE PROCEDURE acct_unlock(OLD.userid,OLD.ownerid,OLD.password,NEW.password); This fails with : [postg...@linux1505 ~]$ cat check_lockout_trig.sql | psql testdb ERROR: syntax error at or near "OLD" LINE 4: EXECUTE PROCEDURE acct_unlock(OLD.userid,OLD.ownerid,OLD.password,NEW.password); What am I doing wrong? Or is there a better way to go about this?
Re: [SQL] Column Specific Update Trigger Routine
Nevermind all, I figured it out Thanks Dmitriy ... From: Dmitriy Igrishin [mailto:dmit...@gmail.com] Sent: Thursday, May 06, 2010 3:25 PM To: Plugge, Joe R. Subject: Re: [SQL] Column Specific Update Trigger Routine Hey Plugge, You dont need to pass OLD.* or NEW.* to the trigger function. These structures available from within trigger function. Note, that in PostgreSQL 9 you will able to create trigger which call function only when some column of the table affected. Regards, Dmitriy 2010/5/7 Plugge, Joe R. mailto:jrplu...@west.com>> I am trying to create a update trigger on a table that basically will only fire when a specific column is updated. I am using version 8.4.3. My plan of attack was to always fire on any row update, and pass in the OLD and NEW column that I want to check. CREATE TRIGGER check_lockout AFTER UPDATE ON acct_table FOR EACH ROW EXECUTE PROCEDURE acct_unlock(OLD.userid,OLD.ownerid,OLD.password,NEW.password); This fails with : [postg...@linux1505 ~]$ cat check_lockout_trig.sql | psql testdb ERROR: syntax error at or near "OLD" LINE 4: EXECUTE PROCEDURE acct_unlock(OLD.userid,OLD.ownerid,OLD.password,NEW.password); What am I doing wrong? Or is there a better way to go about this?
Re: [SQL] Column Specific Update Trigger Routine
On 5/6/2010 4:12 PM, Plugge, Joe R. wrote: > > I am trying to create a update trigger on a table that basically will > only fire when a specific column is updated. I am using version 8.4.3. > > My plan of attack was to always fire on any row update, and pass in > the OLD and NEW column that I want to check. > > CREATE TRIGGER check_lockout > > AFTER UPDATE ON acct_table > > FOR EACH ROW > > EXECUTE PROCEDURE > acct_unlock(OLD.userid,OLD.ownerid,OLD.password,NEW.password); > > This fails with : > > [postg...@linux1505 ~]$ cat check_lockout_trig.sql | psql testdb > > ERROR: syntax error at or near "OLD" > > LINE 4: EXECUTE PROCEDURE > acct_unlock(OLD.userid,OLD.ownerid,OLD.password,NEW.password); > > What am I doing wrong? Or is there a better way to go about this? > You don't call the trigger procedure with the old and new as parameters new and old are automatically created for the function acct_unlock() CREATE TRIGGER check_lockout AFTER UPDATE ON acct_table FOR EACH ROW EXECUTE PROCEDURE acct_unlock(); Next the trigger function would look something like this create or replace function acct_unlock() returns trigger as $$ if (OLD.userid <> NEW.password) do something end if; $$ keep in mind the acct_unlock must be returns trigger Then return either NEW or the OLD record OLD if not changing the record or NEW if the updated values are to be stored in the table. All legitimate Magwerks Corporation quotations are sent in a .PDF file attachment with a unique ID number generated by our proprietary quotation system. Quotations received via any other form of communication will not be honored. CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain legally privileged, confidential or other information proprietary to Magwerks Corporation and is intended solely for the use of the individual to whom it addresses. If the reader of this e-mail is not the intended recipient or authorized agent, the reader is hereby notified that any unauthorized viewing, dissemination, distribution or copying of this e-mail is strictly prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and destroy all occurrences of this e-mail immediately. Thank you.
Re: [SQL] Column Specific Update Trigger Routine
This is what I have and it seems to work: CREATE OR REPLACE FUNCTION holly_unlock() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN IF OLD.password != NEW.password THEN UPDATE hms_mtusers_rw set loginfailedcount = 0 WHERE userid = OLD.userid and ownerid = OLD.ownerid; RETURN NEW; END IF; RETURN NULL; -- result is ignored since this is an AFTER trigger END; $$; Thanks !! From: Justin Graf [mailto:jus...@magwerks.com] Sent: Thursday, May 06, 2010 3:59 PM To: Plugge, Joe R.; pgsql-sql@postgresql.org Subject: Re: [SQL] Column Specific Update Trigger Routine On 5/6/2010 4:12 PM, Plugge, Joe R. wrote: I am trying to create a update trigger on a table that basically will only fire when a specific column is updated. I am using version 8.4.3. My plan of attack was to always fire on any row update, and pass in the OLD and NEW column that I want to check. CREATE TRIGGER check_lockout AFTER UPDATE ON acct_table FOR EACH ROW EXECUTE PROCEDURE acct_unlock(OLD.userid,OLD.ownerid,OLD.password,NEW.password); This fails with : [postg...@linux1505 ~]$ cat check_lockout_trig.sql | psql testdb ERROR: syntax error at or near "OLD" LINE 4: EXECUTE PROCEDURE acct_unlock(OLD.userid,OLD.ownerid,OLD.password,NEW.password); What am I doing wrong? Or is there a better way to go about this? You don't call the trigger procedure with the old and new as parameters new and old are automatically created for the function acct_unlock() CREATE TRIGGER check_lockout AFTER UPDATE ON acct_table FOR EACH ROW EXECUTE PROCEDURE acct_unlock(); Next the trigger function would look something like this create or replace function acct_unlock() returns trigger as $$ if (OLD.userid <> NEW.password) do something end if; $$ keep in mind the acct_unlock must be returns trigger Then return either NEW or the OLD record OLD if not changing the record or NEW if the updated values are to be stored in the table. All legitimate Magwerks Corporation quotations are sent in a .PDF file attachment with a unique ID number generated by our proprietary quotation system. Quotations received via any other form of communication will not be honored. CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain legally privileged, confidential or other information proprietary to Magwerks Corporation and is intended solely for the use of the individual to whom it addresses. If the reader of this e-mail is not the intended recipient or authorized agent, the reader is hereby notified that any unauthorized viewing, dissemination, distribution or copying of this e-mail is strictly prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and destroy all occurrences of this e-mail immediately. Thank you.
Re: [SQL] Column Specific Update Trigger Routine
"Plugge, Joe R." writes: > This is what I have and it seems to work: > IF OLD.password != NEW.password It'd be better to write "IF OLD.password IS DISTINCT FROM NEW.password". The way with != will not do what you want if one value is null and the other isn't. It's possible this doesn't matter in this particular case (if password can't ever be null in this table), but being careful about nulls is a good habit to cultivate. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
