Thread: MS SQL "isDate" equivalent in plpgsql?

Hi,

I am not sure if this is the right newsgroup. If not, please excuse, I
didn't find one, that'ld fit better. *g*

I am porting from MS SQL 7.0 to PostgreSQL. In one Stored Procedure the
function "isDate" is used. IsDate determines, whether the input string is a
date or not:

select isdate('05.15.2003');
Result: 1
select isdate('foobar');
Result: 0

Is there a function in plpgsql like that?
Maybe someone has a self-written function that implements that?

Thanks in advance and regards,
Daniel

Daniel Gehrke wrote:
> I am porting from MS SQL 7.0 to PostgreSQL. In one Stored Procedure the
> function "isDate" is used. IsDate determines, whether the input string is a
> date or not:
>
> select isdate('05.15.2003');
> Result: 1
> select isdate('foobar');
> Result: 0
>
> Is there a function in plpgsql like that?
> Maybe someone has a self-written function that implements that?
>

I wrote something like you describe for someone else. It installs like a
"contrib" library, i.e.:

  put the tarball in your source tree contrib folder
  tar -xzf str_validate.tar.gz
  cd str_validate
  make
  make install
  psql mydatabase < str_validate.sql

You can grab a copy here:
http://www.joeconway.com/

See the README for (a little) more detail, but for your specific
question above, use is like this:

test=# select str_valid('05.15.2003','timestamp'::regtype);
  str_valid
-----------
  t
(1 row)

test=# select str_valid('foobar','timestamp'::regtype);
  str_valid
-----------
  f
(1 row)


Note, the above is for PostgreSQL 7.3.x or 7.4devel. If you are using
7.2.x, grab the version "str_validate for 7.2.X" and use like this:

test=# select str_valid('05.15.2003',(select oid from pg_type where
typname = 'timestamp'));
  str_valid
-----------
  t
(1 row)

test=# select str_valid('foobar',(select oid from pg_type where typname
= 'timestamp'));
  str_valid
-----------
  f
(1 row)

HTH,

Joe