Re: [SQL] Isnumeric function?
How about this
CREATE OR REPLACE FUNCTION is_numeric ( text ) RETURNS bool AS '
if { [string is integer $1] || [string is double $1] } {
return true
}
return false
' LANGUAGE 'pltcl' IMMUTABLE;
SELECT is_numeric ( '-1' );
is_numeric
t
(1 row)
SELECT is_numeric ( '+1e-1' );
is_numeric
t
(1 row)
SELECT is_numeric ( '1.1.1' );
is_numeric
f
(1 row)
--
Vadim Passynkov
-Original Message-
From: Jeff Eckermann [mailto:[EMAIL PROTECTED]
Sent: Thursday, September 09, 2004 10:02 AM
To: Thomas Swan; [EMAIL PROTECTED]
Cc: Josh Berkus; Theo Galanakis; [EMAIL PROTECTED]
Subject: Re: [SQL] Isnumeric function?
Ok, how about this. At least it works in my testing.
I have extended it to allow a negative sign (trailing
also), which I would expect to be allowed in a
comprehensive "isnumeric" function. If I am wrong,
feel free to slap me around; although correcting the
regex would be more constructive. ;-)
create function isnumeric(text) returns boolean as '
select $1 ~
\'(-?([0-9]+\\.?[0-9]*|[0-9]*\\.?[0-9]+)|([0-9]+\\.?[0-9]*|[0-9]*\\.?[0-9]+)
-?)\'
'language 'sql';
--- Thomas Swan <[EMAIL PROTECTED]> wrote:
> Oliver Elphick wrote:
>
> >On Wed, 2004-09-08 at 18:48, Josh Berkus wrote:
> >
> >
> >>Theo, Oliver,
> >>
> >>
> >>
> >>>Any reason why you don't like ~
> '^([0-9]?)+\.?[0-9]*$' ?
> >>>
> >>>
> >>Yes, because it also matches "." , which is not a
> valid numeric value.
> >>
> >>
> >>
> >>> ~ '^([0-9]+|[0-9]+\\.[0-9]*|[0-9]*\\.[0-9]+)$'
> >>>
> >>>
> >>Ah, the brute force approach ;-)
> >>
> >>
> >
> >Nothing like using a nice big hammer!
> >
> >
> >
> Would "^([0-9]+\\.{0,1}[0-9]*|\\.[0-9]+)$" be a
> little cleaner?
>
> >>Actually, the above could be written:
> >>
> >>~ '^([0-9]+)|([0-9]*\\.[0-9]+)$'
> >>
> >>
> >
> >But that doesn't allow a trailing decimal point.
> >
> >
> >
> >>... though that still seems inelegant to me. Is
> there a regex expert in the
> >>house?
> >>
> >>
> >
> >All the elegant approaches I can think of match the
> empty string. There
> >must be at least one digit and 0 or 1 decimal point
> with no other
> >characters permitted. If you use this as a
> constraint, you could make
> >it elegant and combine it with another constraint
> to exclude '' and '.'.
> >
> >
>
>
> ---(end of
> broadcast)---
> TIP 6: Have you searched our list archives?
>
>http://archives.postgresql.org
>
__
Do you Yahoo!?
Yahoo! Mail - 50x more storage than other providers!
http://promotions.yahoo.com/new_mail
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] get sequence value of insert command
> -Original Message-
> From: Erik Thiele [mailto:[EMAIL PROTECTED]
> Sent: Friday, November 19, 2004 3:42 AM
> To: [EMAIL PROTECTED]
> Subject: [SQL] get sequence value of insert command
>
>
> hi
>
> create sequence mysequence;
>
> create table foo(
> id integer default nextval('mysequence'),
> bla text,
> wombat integer,
> foobar date,
> primary key(id)
> );
>
> insert into foo (wombat) values (88);
>
> now how do i know the id of my newly inserted element? and
> how can this be done in a completely concurrency safe way?
CREATE RULE get_pkey_on_insert AS ON INSERT TO foo DO SELECT
currval('mysequence') AS id LIMIT 1;
>
>
> cya
> erik
>
> ---(end of
> broadcast)---
> TIP 7: don't forget to increase your free space map settings
>
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] get sequence value of insert command
> > > create sequence mysequence;
> > >
> > > create table foo(
> > > id integer default nextval('mysequence'),
> > > bla text,
> > > wombat integer,
> > > foobar date,
> > > primary key(id)
> > > );
> > >
> > > insert into foo (wombat) values (88);
> > >
> > > now how do i know the id of my newly inserted element? and
> > > how can this be done in a completely concurrency safe way?
> >
> > CREATE RULE get_pkey_on_insert AS ON INSERT TO foo DO SELECT
> > currval('mysequence') AS id LIMIT 1;
>
> now that one is really great! you should definitly add it to the
> faq. plus an additional explanation why the limit 1 is needed here.
INSERT INTO foo ( ... ) ( SELECT * FROM foo1 );
>
> thanks!
> erik
>
--
Vadim Passynkov
---(end of broadcast)---
TIP 3: 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] Missing SELECT INTO ... DEFAULT VALUES in plpgsql for composite t ypes
Hi all Just self-explanatory code below -- var1 with default value. CREATE DOMAIN var1_type AS pg_catalog.text DEFAULT 'udp'::pg_catalog.text CONSTRAINT "var1_const" CHECK ( VALUE IS NOT NULL AND ( VALUE = 'tcp'::pg_catalog.text OR VALUE = 'udp'::pg_catalog.text ) ); -- var2 without default CREATE DOMAIN var2_type AS pg_catalog.int4 CONSTRAINT "var2_const" CHECK ( VALUE IS NOT NULL AND VALUE > 0 ); -- Let's create composite type foo CREATE TABLE foo ( var1 var1_type, var2 var2_type ); -- and let's create constructor for it CREATE OR REPLACE FUNCTION foo ( int4 ) RETURNS foo AS ' DECLARE this foo; BEGIN /* * I dont want hard coded default * value for this.var1 here * but SELECT INTO this DEFAULT VALUES not possible in plpgsql */ -- SELECT INTO this DEFAULT VALUES; this.var2 := $1; RETURN this; END; ' LANGUAGE 'plpgsql' IMMUTABLE STRICT; SELECT * from foo ( 2 ); var1 | var2 --+-- |2 (1 row) but I want var1 | var2 --+-- udp |2 (1 row) Is anybody know any solution for this? -- Vadim Passynkov ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
