Re: quote_literal(integer) does not exist - Mailing list pgsql-hackers

"Brendan Jurd" <direvus@gmail.com> writes:
> On Nov 26, 2007 5:23 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> ... If anyone wants
>> to make a serious argument for this, look through the whole of
>> pg_proc.h, see what else needs to be changed at the same time,
>> and make a coherent proposal.

> I took your suggestion and looked through all the procs that take a
> text argument.  I honestly didn't see anything else I thought needed
> to change.

> So my proposal is to add your quote_literal(anyelement) SQL function
> to pg_proc and be done with it.

I did the same search.  Ignoring cases where it's fairly obvious that
you're trying to apply a textual operation to something non-textual
(eg, LIKE and btrim(), though both of these have been complained of
since beta started...), it seems that quote_literal() has a good case,
and you could also make an argument for allowing a non-text second
argument for set_config(), since these things used to work:

regression=# select set_config('work_mem', 1000, false);set_config 
------------1000kB
(1 row)

regression=# select set_config('random_page_cost', 2.5, false);set_config 
------------2.5
(1 row)

I don't find that amazingly compelling, but it's not silly either.
If we were to do this I think I'd introduce set_config(text,float8,bool)
rather than going all the way with anyelement, though.  That would be
enough to cover both the int and float cases, as well as anyone who
likes to spell booleans as 1 and 0.

I don't offhand see anything else I'd consider weakening the casting
rules for.  If anyone else is interested, I took

select p.oid::regprocedure as regprocedure, oprname from pg_proc p left
join pg_operator o on (oprcode = p.oid) where 25 = any (proargtypes)

and removed duplicates and obviously-internal functions such as textout,
leaving me with this list:
                      regprocedure                        | oprname 
-----------------------------------------------------------+---------array_to_string(anyarray,text)
      | btrim(text)                                               | btrim(text,text)
     | character_length(text)                                    | convert_to(text,name)
    | initcap(text)                                             | length(text)
   | lower(text)                                               | lpad(text,integer)
  | lpad(text,integer,text)                                   | ltrim(text)
 | ltrim(text,text)                                          | md5(text)
| octet_length(text)                                        | overlay(text,text,integer)
|overlay(text,text,integer,integer)                        | position(text,text)
|quote_ident(text)                                         | quote_literal(text)
|regexp_matches(text,text)                                 | regexp_matches(text,text,text)
|regexp_replace(text,text,text)                            | regexp_replace(text,text,text,text)
|regexp_split_to_array(text,text)                          | regexp_split_to_array(text,text,text)
|regexp_split_to_table(text,text)                          | regexp_split_to_table(text,text,text)
|repeat(text,integer)                                      | replace(text,text,text)
|rpad(text,integer)                                        | rpad(text,integer,text)
|rtrim(text)                                               | rtrim(text,text)
|set_config(text,text,boolean)                             | split_part(text,text,integer)
|string_to_array(text,text)                                | strpos(text,text)
|substr(text,integer)                                      | substr(text,integer,integer)
|substring(text,integer)                                   | substring(text,integer,integer)
|substring(text,text)                                      | substring(text,text,text)
|texticlike(text,text)                                     | ~~*texticnlike(text,text)
 | !~~*texticregexeq(text,text)                                  | ~*texticregexne(text,text)
      | !~*textlike(text,text)                                       | ~~textnlike(text,text)
          | !~~textregexeq(text,text)                                    | ~textregexne(text,text)
             | !~upper(text)                                               | 
 


Thoughts?
        regards, tom lane