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