Обсуждение: case dumbiness in return from functions
After discovering that pg_get_serial_sequence behaves in a bit
strange way[1] when it deals to case sensitiveness... I just
discovered that you've the same behaviour for any function... at
least in PHP.
postgresql
Versione: 8.1.11
php:
Versione: 5.2.0
eg.
create or replace function testA(out pIpPo int) as
$$
begin
pIpPo:=7;
return;
end;
$$ language plpgsql;
...
$result=pg_query('select pIpPo from testA()');
$row=pg_fetch_array($result);
print(var_export($row));
array ( 'pippo' => '7', )
that makes
$ImSoSad=$row['pIpPo'];
return null
And 2h went trying to understand where a session went lost :(
Fortunately it can be fixed at a small price with an alias... but
still I find it a pain.
Whan you've identifiers composed of more than 2 words, camel case can
make your code lines much shorter.
Please, please, please... fix this. Minor things like this can make
coding in Postgres MUCH more enjoyable.
[1] this is documented... is this awwwww bwaaaa bwaaaaaa behaviour of
functions documented as well?
--
Ivan Sergio Borgonovo
http://www.webthatworks.it
Ivan Sergio Borgonovo <mail@webthatworks.it> writes:
> After discovering that pg_get_serial_sequence behaves in a bit
> strange way[1] when it deals to case sensitiveness
The SQL standard specifies that unquoted identifiers are case-insensitive.
You're welcome to spell them as camelCase in your source code if you
feel like it, but don't expect that PG, or any other SQL-compliant
database, will pay attention.
regards, tom lane
On Thu, 17 Jan 2008 19:07:59 -0500 Tom Lane <tgl@sss.pgh.pa.us> wrote: > Ivan Sergio Borgonovo <mail@webthatworks.it> writes: > > After discovering that pg_get_serial_sequence behaves in a bit > > strange way[1] when it deals to case sensitiveness > > The SQL standard specifies that unquoted identifiers are > case-insensitive. You're welcome to spell them as camelCase in your > source code if you feel like it, but don't expect that PG, or any > other SQL-compliant database, will pay attention. OK... I did get tricked mixing in the same code base (same file) access to MS SQL and pg and I confused a select PiPpO from table in MS SQL and a select PiPpO from function() in pg And I thought that select from table behave differently from select from functions. Having met the admittedly strange behaviour in pg_get_serial_sequence that is not case-insensitive I got confused. All the other select were in fact aliased. I noticed that the argument of case insensitivity vs. case preservation is recurrent and I'm not going to argue about it. I'd prefer to have case preservation but I'd prefer to be able to fly too. I bet there are good reasons for both even if hope is not going to die. Sorry, it was not meant to be disrespectful of the good work you're doing. -- Ivan Sergio Borgonovo http://www.webthatworks.it
Tom Lane ha scritto: > Ivan Sergio Borgonovo <mail@webthatworks.it> writes: > >> After discovering that pg_get_serial_sequence behaves in a bit >> strange way[1] when it deals to case sensitiveness >> > > The SQL standard specifies that unquoted identifiers are case-insensitive. > You're welcome to spell them as camelCase in your source code if you > feel like it, but don't expect that PG, or any other SQL-compliant > database, will pay attention. > > regards, tom lane > > yet I find disturbing that Postgres doesn't make the effort to respect the case specified by the user. If I created a field called "REF" why should Postgres call it "ref" in the output of queries if the standard doesn't specify any obligation to convert the name ? I'd like to have the possibility to enable this feature in future releases. As for portability: it may not be a concern when you have at disposal the best DB around :)
On Fri, 18 Jan 2008 13:14:33 +0100 Nico Sabbi <nsabbi@officinedigitali.it> wrote: > yet I find disturbing that Postgres doesn't make the effort > to respect the case specified by the user. If I created a field > called "REF" why should Postgres call it "ref" in the output of > queries if the standard doesn't specify any obligation to convert > the name ? I'd like to have the possibility to enable this feature > in future releases. http://archives.postgresql.org/pgsql-hackers/2006-10/msg01527.php There are pro and cons. In my dreams I'd like a good Genius to fix all the issues even the one I can barely understand and have a case preserving pg. In reality I'll take more care with quotations and never post after midnight. -- Ivan Sergio Borgonovo http://www.webthatworks.it
Nico Sabbi wrote:
>
> Tom Lane ha scritto:
> > Ivan Sergio Borgonovo <mail@webthatworks.it> writes:
> >
> >> After discovering that pg_get_serial_sequence behaves in a bit
> >> strange way[1] when it deals to case sensitiveness
> >>
> >
> > The SQL standard specifies that unquoted identifiers are case-insensitive.
> > You're welcome to spell them as camelCase in your source code if you
> > feel like it, but don't expect that PG, or any other SQL-compliant
> > database, will pay attention.
> >
> > regards, tom lane
>
> yet I find disturbing that Postgres doesn't make the effort
> to respect the case specified by the user. If I created a field
> called "REF" why should Postgres call it "ref" in the output of queries
> if the standard doesn't specify any obligation to convert the name ?
> I'd like to have the possibility to enable this feature in future releases.
Why should it PostgreSQL "make the effort" ?
Tom was _very_ clear in the SQL standard.
Oracle and Informix also ignore your capitals, although they behave slightly differently in forcing things to upper or lower case. In Informix:
create table FOO (FooId SERIAL PRIMARY KEY);
INFO - foo: Columns Indexes Privileges References Status ...
Display column names and data types for a table.
----------------------- billing@arches_ip ------ Press CTRL-W for Help --------
Column name Type Nulls
fooid serial no
Bottom line: well documented in all databases I have seen. Not very important -- if you really care (why on earth would you?) then double quote things like table and column names.
I would _far_ rather have developers work on resolving subtle issues, or adding *useful* features than this sort of basura.
Greg Williamson
Senior DBA
GlobeXplorer LLC, a DigitalGlobe company
Confidentiality Notice: This e-mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information and must be protected in accordance with those provisions. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.
(My corporate masters made me say this.)
On Jan 18, 2008, at 1:14 PM, Nico Sabbi wrote:
> Tom Lane ha scritto:
>> The SQL standard specifies that unquoted identifiers are case-
>> insensitive.
^^^^^^^^
>> You're welcome to spell them as camelCase in your source code if you
>> feel like it, but don't expect that PG, or any other SQL-compliant
>> database, will pay attention.
>>
>> regards, tom lane
> yet I find disturbing that Postgres doesn't make the effort
> to respect the case specified by the user. If I created a field
> called "REF" why should Postgres call it "ref" in the output of
> queries
> if the standard doesn't specify any obligation to convert the name ?
If you want to use case sensitive identifiers, then quote them! It's
not that hard. In your example above you're doing just that, so your
statement does not even apply ;)
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.
!DSPAM:737,47909a669491882451502!
Nico Sabbi wrote: > yet I find disturbing that Postgres doesn't make the effort > to respect the case specified by the user. It does -- if you quote the names. > If I created a field > called "REF" why should Postgres call it "ref" in the output of queries > if the standard doesn't specify any obligation to convert the name ? Actually I think the standard mandates case-folding (though to upper case rather than lower, i.e. the other way around) -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
Alvaro Herrera <alvherre@commandprompt.com> writes:
> Actually I think the standard mandates case-folding (though to upper
> case rather than lower, i.e. the other way around)
That's how I read it too. SQL99 5.2 saith
22) The case-normal form of the <identifier body> of a <regular
identifier> is used for purposes such as and including
determination of identifier equivalence, representation in
the Definition and Information Schemas, and representation in
diagnostics areas.
NOTE 44 - Any lower-case letters for which there are no upper-
case equivalents are left in their lower-case form.
23) The <identifier body> of a <regular identifier> is equivalent
to an <identifier body> in which every letter that is a lower-
case letter is replaced by the equivalent upper-case letter or
letters. This treatment includes determination of equivalence,
representation in the Information and Definition Schemas,
representation in the diagnostics area, and similar uses.
In particular this appears to me to specifically require that column
names returned by a SELECT be case-normalized, which is what the OP
would like us to not do.
regards, tom lane