

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 :-))


Wed, 29 Sep 1999 14:32:00 +0200 (CEST)
Zakkr <zakkr@zf.jcu.cz>
string function


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


Wed, 29 Sep 1999 15:31:29 +0200 (CEST)
Zakkr <zakkr@zf.jcu.cz>
Re: [HACKERS] string function
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.

Wed, 29 Sep 1999 10:07:14 -0400 (EDT)
"D'Arcy" "J.M." Cain <darcy@druid.net>
Re: [HACKERS] string function
> 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

Wed, 29 Sep 1999 15:30:26 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: [HACKERS] string function


> 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 


Wed, 29 Sep 1999 17:50:25 +0200 (CEST)
Zakkr <zakkr@zf.jcu.cz>
Re: [HACKERS] string function
> 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

Thu, 30 Sep 1999 06:08:18 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: [HACKERS] string function


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


Thu, 30 Sep 1999 10:19:00 +0200 (CEST)
Zakkr <zakkr@zf.jcu.cz>
Re: [HACKERS] string function

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
						 


Thu, 30 Sep 1999 19:13:12 +0200 (CEST)
Zakkr <zakkr@zf.jcu.cz>
TO_CHAR()
> 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

Fri, 01 Oct 1999 02:57:06 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: TO_CHAR()


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


Fri, 1 Oct 1999 09:43:14 +0200 (CEST)
Zakkr <zakkr@zf.jcu.cz>
Re: [HACKERS] Re: TO_CHAR()
> > > 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

Fri, 01 Oct 1999 14:54:45 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: [HACKERS] Re: TO_CHAR()
> > 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) #



Fri, 1 Oct 1999 18:00:09 +0200 (MET DST)
wieck@debis.com (Jan Wieck)
Re: [HACKERS] Re: TO_CHAR()
-----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 sure that 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-----

01 Oct 1999 21:58:25 -0400
Roland Roberts <roberts@panix.com>
Re: [HACKERS] Re: TO_CHAR()
>     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

Sat, 02 Oct 1999 14:48:11 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: [HACKERS] Re: TO_CHAR()