Обсуждение: grouping treated as keyword in function return table
Postgres 9.5.3 built from source. However, I think this has been
happening for a long time.
create function a() returns table( grouping integer ) language sql as $$
select 1;
$$;
ERROR: syntax error at or near "grouping"
LINE 1: create function a() returns table( grouping integer ) langua...
If we quote "grouping"
^
create function a() returns table( "grouping" integer ) language sql as $$
select 1;
$$
;
CREATE FUNCTION
grouping is a valid column in a table and does not need to be quoted:
create temp table test( grouping integer );
CREATE TABLE
So, in almost all cases grouping is not required to be quoted,
however when used in a table defined as a function return type, it
is. Additionally, pg_dumpall does not quote grouping when dumping
the function so the function will not be restored when the dump is
loaded.
Thanks,
Mike
-
Mike Porter
PGP Fingerprint: F4 AE E1 9F 67 F7 DA EA 2F D2 37 F3 99 ED D1 C2
Mike Porter <mike@udel.edu> writes:
> Postgres 9.5.3 built from source. However, I think this has been
> happening for a long time.
Only since 9.5, because GROUPING wasn't a keyword before that.
> So, in almost all cases grouping is not required to be quoted,
> however when used in a table defined as a function return type, it
> is.
This is expected given that it's now partially reserved.
> Additionally, pg_dumpall does not quote grouping when dumping
> the function so the function will not be restored when the dump is
> loaded.
That would be a pg_dump bug, but it doesn't happen for me: I get
CREATE FUNCTION a() RETURNS TABLE("grouping" integer)
LANGUAGE sql
AS $$ select 1; $$;
Sure you're using 9.5 pg_dump?
regards, tom lane
On Tue, 12 Jul 2016, Tom Lane wrote:
> Mike Porter <mike@udel.edu> writes:
>> Postgres 9.5.3 built from source. However, I think this has been
>> happening for a long time.
>
> Only since 9.5, because GROUPING wasn't a keyword before that.
>
>> So, in almost all cases grouping is not required to be quoted,
>> however when used in a table defined as a function return type, it
>> is.
>
> This is expected given that it's now partially reserved.
Perhaps it was in an earlier 9.5 test when I added the quotes...
I noticed that grouping is fully quoted in all the tables in the
dump. Will grouping be a fully reserved word at some point?
>
>> Additionally, pg_dumpall does not quote grouping when dumping
>> the function so the function will not be restored when the dump is
>> loaded.
>
> That would be a pg_dump bug, but it doesn't happen for me: I get
>
> CREATE FUNCTION a() RETURNS TABLE("grouping" integer)
> LANGUAGE sql
> AS $$ select 1; $$;
>
> Sure you're using 9.5 pg_dump?
My notes say so.
/usr/local/postgresql-9.5.3/bin/pg_dumpall -h a.server -U postgres > 9.5.3.sql
From the dump:
--
-- Name: find_attributes_source(character varying, character varying, integer); Type: FUNCTION; Schema: public; Owner:
postgres
--
CREATE FUNCTION find_attributes_source(a_alphaname character varying DEFAULT NULL::character varying, a_emplid
charactervarying DEFAULT NULL::character varying, a_nssid integer DEFAULT NULL::integer) RETURNS TABLE(nssid integer,
alphanamecharacter varying, emplid character varying, grouping_id integer, grouping character varying, programatic
boolean,g_cmt character varying, priority integer, acon_id integer, acon character varying, acon_cmt character varying,
common_idinteger, common character varying, multivalue boolean, common_cmt character varying, attribute_id integer,
valuecharacter varying, attribute_cmt character varying)
LANGUAGE sql STABLE COST 50 ROWS 50
AS $_$
=> /usr/local/postgresql-9.5.3/bin/pg_dumpall -V
pg_dumpall (PostgreSQL) 9.5.3
The source database on "a.server" is 9.3.5.
>
> regards, tom lane
Thanks,
Mike
>
-
Mike Porter
PGP Fingerprint: F4 AE E1 9F 67 F7 DA EA 2F D2 37 F3 99 ED D1 C2
Mike Porter <mike@udel.edu> writes:
> On Tue, 12 Jul 2016, Tom Lane wrote:
>> Only since 9.5, because GROUPING wasn't a keyword before that.
> I noticed that grouping is fully quoted in all the tables in the
> dump. Will grouping be a fully reserved word at some point?
Well, we won't voluntarily make it so, but the SQL committee seems not to
care when inventing new syntax whether it requires fully reserving new
keywords to make it work. So it's a matter of what they do and whether
we decide we want to adopt the feature/syntax.
As far as dumps go, our default practice is to quote any non-unreserved
keyword; it's not worth the trouble to decide for partially-reserved
words whether they'd really need to be quoted in the specific context.
>> Sure you're using 9.5 pg_dump?
> My notes say so.
> /usr/local/postgresql-9.5.3/bin/pg_dumpall -h a.server -U postgres > 9.5.3.sql
Hm, is the source server 9.5? I think that the TABLE clause is actually
generated server-side in this case.
When you're making a dump you intend to load into a different server
version, we recommend using --quote-all-identifiers to make sure you're
not blindsided by identifiers that have become reserved words in the
newer server version.
regards, tom lane
On Tue, 12 Jul 2016, Tom Lane wrote: > Mike Porter <mike@udel.edu> writes: >> On Tue, 12 Jul 2016, Tom Lane wrote: >>> Only since 9.5, because GROUPING wasn't a keyword before that. >>> Sure you're using 9.5 pg_dump? > >> My notes say so. >> /usr/local/postgresql-9.5.3/bin/pg_dumpall -h a.server -U postgres > 9.5.3.sql > > Hm, is the source server 9.5? I think that the TABLE clause is actually > generated server-side in this case. The source server is 9.3.5 (9.3...) > When you're making a dump you intend to load into a different server > version, we recommend using --quote-all-identifiers to make sure you're > not blindsided by identifiers that have become reserved words in the > newer server version. Great advice. Thanks, Mike > > regards, tom lane > - Mike Porter PGP Fingerprint: F4 AE E1 9F 67 F7 DA EA 2F D2 37 F3 99 ED D1 C2