Thread: string function
Hi,
I wrote new buildin functions (inspired with ...src/backend/utils/adt):
text *asterisk(text *string) - Returns string, with all letters is
'*' and length of new string is equal
to original string.
text *pgcrypt(text *string) - Returns string, cryped via DES crypt(3).
Do somebody want this func. ? I try write more string func. if will
interest.. (in PSQL is not strcat(), md5() ...etc.).
Zakkr
PS. sorry I new in PSQL development, but PSQL programing is very interest
for me :-))
Thus spake Zakkr
> text *pgcrypt(text *string) - Returns string, cryped via DES crypt(3).
>
>
> Do somebody want this func. ? I try write more string func. if will
> interest.. (in PSQL is not strcat(), md5() ...etc.).
Careful. This could affect the ability to distribute the code world wide.
I know that the server is in Canada but we have mirrors in the US.
--
D'Arcy J.M. Cain <darcy@{druid|vex}.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.
On Wed, 29 Sep 1999, D'Arcy J.M. Cain wrote:
> Thus spake Zakkr
> > text *pgcrypt(text *string) - Returns string, cryped via DES crypt(3).
> >
> >
> > Do somebody want this func. ? I try write more string func. if will
> > interest.. (in PSQL is not strcat(), md5() ...etc.).
>
> Careful. This could affect the ability to distribute the code world wide.
> I know that the server is in Canada but we have mirrors in the US.
Yes, I know unimaginable US restrictions... pgcrypt() is experiment for me
(I need it in my project) - I want write other func. for world wide.
Try somebody implemeny strftime(),strcat() to PSQL ? -
(is any problem in PSQL, that not exist more string/date/..etc functions or
is problem with programmer absence (only) ?)
Zakkr
> Try somebody implemeny strftime(),strcat() to PSQL ? -
> (is any problem in PSQL, that not exist more string/date/..etc functions or
> is problem with programmer absence (only) ?)
It is some combination of: 1) a mild reluctance to add too many
specialty functions, 2) no specific need, at least from a programmer
who can make it happen, 3) unclear tradeoffs between bigger hammers
causing more damage than they help, and 4) existing functionality
which already does it. Oh, and 5) simply that no one has done it yet!
In (almost) all cases, extensions can be put into the contrib area,
and that is a good way to test out new functionality. In some cases,
new functionality should go into the main tree directly. Of the cases
you just mentioned:
1) strftime() allows arbitrary formatting of date/time strings.
Certainly useful, though one can easily format a string that is no
longer recognizable to Postgres as a date which is one reason why I
didn't code it up previously. Perhaps we should focus on an
Oracle-compatible routine for this; I think it uses tochar() to do
formatting. Someone recently volunteered to send in code which could
be used for this, but I haven't seen the code yet :(
2) strcat() concatenates two strings? There is a full set of functions
which do this, and they are used to support the SQL92 concatenation
operator "||".
But in general, more functionality is A Good Thing, and discussing it
on the hackers list is a good way to get people used to a new idea, or
to evolve a new idea into something people like even better.
- Thomas
--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California
> 1) strftime() allows arbitrary formatting of date/time strings.
> Certainly useful, though one can easily format a string that is no
> longer recognizable to Postgres as a date which is one reason why I
> didn't code it up previously. Perhaps we should focus on an
> Oracle-compatible routine for this; I think it uses tochar() to do
> formatting. Someone recently volunteered to send in code which could
> be used for this, but I haven't seen the code yet :(
If I good understand you, you don't reject strftime idea. I try it..
> 2) strcat() concatenates two strings? There is a full set of functions
> which do this, and they are used to support the SQL92 concatenation
> operator "||".
:-)) yes '||' is good. I said it bad. I think exapmle inetcat() (I
programming this for my project):
select inetcat('160.217.1.0/24', 50);
inetcat
------------
160.217.1.50
In my prev. letter I said it generally. (I'am finding a more function
in PSQL.. and I can try write it.).
Zakkr
> If I good understand you, you don't reject strftime idea. I try it..
But I was trying to nudge you to look at a slightly different idea
which would be compatible with Oracle, for no particularly good reason
other than that would help some folks when they try to port apps over
to Postgres.
- Thomas
--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California
On Thu, 30 Sep 1999, Thomas Lockhart wrote:
> > If I good understand you, you don't reject strftime idea. I try it..
>
> But I was trying to nudge you to look at a slightly different idea
> which would be compatible with Oracle, for no particularly good reason
> other than that would help some folks when they try to port apps over
> to Postgres.
My first idea was write to PSQL strftime full compatible with 'C'
strftime(). Now I see Oracle documentation for TO_CHAR(), ..hmm it is not
easy, it is very specific (unique) function, but I agree - your idea
(compatible with Oracle) is better :-)). I try to_char()..
Think for your time Thomas.
Zakkr
Hi,
this is my new (experimental) TO_CHAR() function (compatible with oracle),
it is available on: ftp://ftp2.zf.jcu.cz/users/zakkr/pg/TO_CHAR-0.1.tar.gz.
See example:
=======
abil=> select to_char('now', 'HH:MI:SS Day MON CC');
to_char
------------------------
20:12:02 Thursday Sep 19
abil=> select to_char('now', 'MM MON Month MONTH YYYY Y,YYY YYY YY Y');
to_char
----------------------------------------------
09 Sep September SEPTEMBER 1999 1,999 999 99 9
abil=> select to_char('now', 'DDD D WW SSSS'); to_char
--------------
273 4 39 72810
abil=> select to_char('now', 'hello year YYYY');
to_char
---------------
hello year 1999
========Any comments ? Zakkr
> this is my new (experimental) TO_CHAR() function (compatible with oracle),
> it is available on: ftp://ftp2.zf.jcu.cz/users/zakkr/pg/TO_CHAR-0.1.tar.gz.
> Any comments ?
Nice! So, there is a routine to go the other way in Oracle
(format()??) and if we have both then we're cookin'
- Thomas
--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California
On Fri, 1 Oct 1999, Thomas Lockhart wrote:
> > this is my new (experimental) TO_CHAR() function (compatible with oracle),
> > it is available on: ftp://ftp2.zf.jcu.cz/users/zakkr/pg/TO_CHAR-0.1.tar.gz.
> > Any comments ?
>
> Nice! So, there is a routine to go the other way in Oracle
> (format()??) and if we have both then we're cookin'
Thank! But.. sorry, I don't understand you. What other way in the Oracle
(format()??) ?
What make with this code next? Is it interesting for developers (hmm, to this
discussion join you (Thomas) and me only, but others probably needn't
TO_CHAR, TO_NUMBER, TO_DATE) ..?
Zakkr
> > > this is my new (experimental) TO_CHAR() function (compatible with oracle),
> > > it is available on: ftp://ftp2.zf.jcu.cz/users/zakkr/pg/TO_CHAR-0.1.tar.gz.
> > > Any comments ?
> > Nice! So, there is a routine to go the other way in Oracle
> > (format()??) and if we have both then we're cookin'
> Thank! But.. sorry, I don't understand you. What other way in the Oracle
> (format()??) ?
Ah, something to go from a random character string to an internal
date/time type. We have a fairly generic way to do this already, but
since to_char() can insert random garbage at the user's behest then it
would be nice to have a related routine which can be told how to
decode a string containing random garbage. I'm pretty sure that Oracle
has such a beast, but I don't have the docs. I would think that it
could be done as a thin layer on top of datetime_in().
> What make with this code next? Is it interesting for developers (hmm, to this
> discussion join you (Thomas) and me only, but others probably needn't
> TO_CHAR, TO_NUMBER, TO_DATE) ..?
People have requested to_char(), or at least inquired about it, though
of course there are always ways to work around not having it. After
all, it *is* non-standard ;) But we already have some Oracle
compatibility functions, and a few more won't hurt.
There are two possibilities:
1) we incorporate it into the main tree
2) we distribute it as a contrib package
I'd prefer the former, though right now the code has problems since it
converts input to timestamp to take advantage of localtime(). Can you
look at, and perhaps use directly, datetime2tm()? That should get you
the structure you need to work with, and it is not limited to just
Unix system time ranges. Just be aware that the year field contains a
real year, not year modulo 1900 as in a real Unix tm structure.
Let me know if this is possible.
- Thomas
--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California
> > discussion join you (Thomas) and me only, but others probably needn't
> > TO_CHAR, TO_NUMBER, TO_DATE) ..?
>
> People have requested to_char(), or at least inquired about it, though
> of course there are always ways to work around not having it. After
> all, it *is* non-standard ;) But we already have some Oracle
> compatibility functions, and a few more won't hurt.
>
> There are two possibilities:
>
> 1) we incorporate it into the main tree
> 2) we distribute it as a contrib package
If incorporating into main tree, don't forget that TO_CHAR()
must also be capable to handle NUMERIC/DECIMAL/INTEGER with a
rich set of fomatting styles. Actually I'm in doubt if you
both are a little too much focusing on DATE/TIME.
This means that there could be different input arguments
(type and number!) to TO_CHAR().
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#========================================= wieck@debis.com (Jan Wieck) #
-----BEGIN PGP SIGNED MESSAGE----- >>>>> "Thomas" == Thomas Lockhart <lockhart@alumni.caltech.edu> writes: Thomas> Ah, something to go from a random character string to an Thomas> internal date/time type. [...] I'm pretty surethat Oracle Thomas> has such a beast, but I don't have the docs. Just FYI, it's to_date(). roland - -- PGP Key ID: 66 BC 3B CD Roland B. Roberts, PhD Custom Software Solutions roberts@panix.com 76-15 113th Street, Apt 3B rbroberts@acm.org Forest Hills, NY 11375 -----BEGIN PGP SIGNATURE----- Version: 2.6.3a Charset: noconv Comment: Processed by Mailcrypt 3.5.4, an Emacs/PGP interface iQCVAwUBN/VmwOoW38lmvDvNAQElOQP/dFLgEyjpuKrtF9Ahu682joAegub4TbyW RJUT8oVoMgchw0iIhZ4d5y6X7PNYc0ynJfdd5DmIawJuCdw79fvmpQrl+XVkft33 78mTJFkSyilqYfl/uT2zq5i+P/k6ARZYYJ+OpvUIJG0ttuDit5Xf/LRIM3N+UJ6l mATOFpUCn9E= =kmuG -----END PGP SIGNATURE-----
> If incorporating into main tree, don't forget that TO_CHAR()
> must also be capable to handle NUMERIC/DECIMAL/INTEGER with a
> rich set of fomatting styles. Actually I'm in doubt if you
> both are a little too much focusing on DATE/TIME.
> This means that there could be different input arguments
> (type and number!) to TO_CHAR().
Not a problem. In some cases, we are only an alias away from having it
(e.g. to_char(int) == text(int4)). Not sure about *all* of the others,
but the ugliest will be the to_char(datetime) and to_date(text,format)
stuff, so that is a good place to start.
- Thomas
--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California