options.sql
application/octet-stream
Filename: options.sql
Type: application/octet-stream
Part: 4
/*
* --------------------------------------------------------------------------
* Commands either completely without parameters, or without defelem or similar
* parameters which are matched using string comparisons, and thus left out of
* scope of this patch.
* --------------------------------------------------------------------------
*/
-- ABORT — abort the current transaction
-- ALTER AGGREGATE — change the definition of an aggregate function
-- ALTER COLLATION — change the definition of a collation
-- ALTER CONVERSION — change the definition of a conversion
-- ALTER DOMAIN — change the definition of a domain
-- ALTER EVENT TRIGGER — change the definition of an event trigger
-- ALTER EXTENSION — change the definition of an extension
-- ALTER LANGUAGE — change the definition of a procedural language
-- ALTER LARGE OBJECT — change the definition of a large object
-- ALTER MATERIALIZED VIEW — change the definition of a materialized view
-- ALTER OPERATOR CLASS — change the definition of an operator class
-- ALTER OPERATOR FAMILY — change the definition of an operator family
-- ALTER POLICY — change the definition of a row level security policy
-- ALTER RULE — change the definition of a rule
-- ALTER SCHEMA — change the definition of a schema
-- ALTER STATISTICS — change the definition of an extended statistics object
-- ALTER TEXT SEARCH CONFIGURATION — change the definition of a text search configuration
-- ALTER TEXT SEARCH PARSER — change the definition of a text search parser
-- ALTER TEXT SEARCH TEMPLATE — change the definition of a text search template
-- ALTER TRIGGER — change the definition of a trigger
-- ALTER TYPE — change the definition of a type
-- ANALYZE — collect statistics about a database
-- CALL — invoke a procedure
-- CHECKPOINT — force a write-ahead log checkpoint
-- CLOSE — close a cursor
-- CLUSTER — cluster a table according to an index
-- COMMENT — define or change the comment of an object
-- COMMIT — commit the current transaction
-- COMMIT PREPARED — commit a transaction that was earlier prepared for two-phase commit
-- CREATE ACCESS METHOD — define a new access method
-- CREATE CAST — define a new cast
-- CREATE CONVERSION — define a new encoding conversion
-- CREATE DOMAIN — define a new domain
-- CREATE FOREIGN TABLE — define a new foreign table
-- CREATE LANGUAGE — define a new procedural language
-- CREATE MATERIALIZED VIEW — define a new materialized view
-- CREATE OPERATOR CLASS — define a new operator class
-- CREATE POLICY — define a new row level security policy for a table
-- CREATE PROCEDURE — define a new procedure
-- CREATE RULE — define a new rewrite rule
-- CREATE SCHEMA — define a new schema
-- CREATE TRANSFORM — define a new transform
-- CREATE TRIGGER — define a new trigger
-- DEALLOCATE — deallocate a prepared statement
-- DECLARE — define a cursor
-- DELETE — delete rows of a table
-- DISCARD — discard session state
-- DO — execute an anonymous code block
-- DROP ACCESS METHOD — remove an access method
-- DROP AGGREGATE — remove an aggregate function
-- DROP CAST — remove a cast
-- DROP COLLATION — remove a collation
-- DROP CONVERSION — remove a conversion
-- DROP DATABASE — remove a database
-- DROP DOMAIN — remove a domain
-- DROP EVENT TRIGGER — remove an event trigger
-- DROP EXTENSION — remove an extension
-- DROP FOREIGN DATA WRAPPER — remove a foreign-data wrapper
-- DROP FOREIGN TABLE — remove a foreign table
-- DROP FUNCTION — remove a function
-- DROP GROUP — remove a database role
-- DROP INDEX — remove an index
-- DROP LANGUAGE — remove a procedural language
-- DROP MATERIALIZED VIEW — remove a materialized view
-- DROP OPERATOR — remove an operator
-- DROP OPERATOR CLASS — remove an operator class
-- DROP OPERATOR FAMILY — remove an operator family
-- DROP OWNED — remove database objects owned by a database role
-- DROP POLICY — remove a row level security policy from a table
-- DROP PROCEDURE — remove a procedure
-- DROP PUBLICATION — remove a publication
-- DROP ROLE — remove a database role
-- DROP ROUTINE — remove a routine
-- DROP RULE — remove a rewrite rule
-- DROP SCHEMA — remove a schema
-- DROP SEQUENCE — remove a sequence
-- DROP SERVER — remove a foreign server descriptor
-- DROP TABLE — remove a table
-- DROP TEXT SEARCH CONFIGURATION — remove a text search configuration
-- DROP TEXT SEARCH DICTIONARY — remove a text search dictionary
-- DROP TEXT SEARCH PARSER — remove a text search parser
-- DROP TEXT SEARCH TEMPLATE — remove a text search template
-- DROP TRANSFORM — remove a transform
-- DROP STATISTICS — remove extended statistics
-- DROP SUBSCRIPTION — remove a subscription
-- DROP TABLESPACE — remove a tablespace
-- DROP TRIGGER — remove a trigger
-- DROP TYPE — remove a data type
-- DROP USER — remove a database role
-- DROP VIEW — remove a view
-- END — commit the current transaction
-- EXECUTE — execute a prepared statement
-- FETCH — retrieve rows from a query using a cursor
-- GRANT — define access privileges
-- LISTEN — listen for a notification
-- LOAD — load a shared library file
-- LOCK — lock a table
-- MOVE — position a cursor
-- NOTIFY — generate a notification
-- PREPARE — prepare a statement for execution
-- PREPARE TRANSACTION — prepare the current transaction for two-phase commit
-- REASSIGN OWNED — change the ownership of database objects owned by a database role
-- REFRESH MATERIALIZED VIEW — replace the contents of a materialized view
-- REINDEX — rebuild indexes
-- RELEASE SAVEPOINT — destroy a previously defined savepoint
-- REVOKE — remove access privileges
-- ROLLBACK — abort the current transaction
-- ROLLBACK PREPARED — cancel a transaction that was earlier prepared for two-phase commit
-- ROLLBACK TO SAVEPOINT — roll back to a savepoint
-- SAVEPOINT — define a new savepoint within the current transaction
-- SECURITY LABEL — define or change a security label applied to an object
-- SET CONSTRAINTS — set constraint check timing for the current transaction
-- SET ROLE — set the current user identifier of the current session
-- TRUNCATE — empty a table or set of tables
-- UNLISTEN — stop listening for a notification
-- VACUUM — garbage-collect and optionally analyze a database
-- VALUES — compute a set of rows
/*
* --------------------------------------------------------------------------
* Commands using parameters with case insensitive matches on user input via
* reloptions
* --------------------------------------------------------------------------
*/
-- CREATE TABLESPACE — define a new tablespace
create tablespace ts1 location '/tmp/ts' with (seq_page_cost = 1);
drop tablespace ts1;
create tablespace ts1 location '/tmp/ts' with (Seq_page_cost = 1);
drop tablespace ts1;
create tablespace ts1 location '/tmp/ts' with ("Seq_page_cost" = 1);
create tablespace ts1 location '/tmp/ts' with ("seq_page_cost" = 1);
-- ALTER TABLESPACE — change the definition of a tablespace
alter tablespace ts1 set (seq_page_cost = 1);
alter tablespace ts1 set (Seq_page_cost = 1);
alter tablespace ts1 set ("seq_page_cost" = 1);
alter tablespace ts1 set ("Seq_page_cost" = 1);
-- CREATE TABLE AS — define a new table from the results of a query
create table tas1 with (fillfactor = 10) as select 1 a;
create table tas2 with ("Fillfactor" = 10) as select 1 a;
-- CREATE TABLE — define a new table
create table t1 (a integer, b integer) with (oids);
create table t2 (a integer, b integer) with (Oids);
create table t3 (a integer, b integer) with ("oids");
create table t4 (a integer, b integer) with ("Oids");
-- ALTER TABLE — change the definition of a table
alter table t2 set (fillfactor = 10);
alter table t2 set (Fillfactor = 10);
alter table t2 set ("fillfactor" = "10");
alter table t2 set ("fillfactor" = " 10 ");
alter table t2 set ("Fillfactor" = 10);
alter table t2 set ("Fillfactor" = "10");
-- CREATE INDEX — define a new index
create index idx1 on t1 (a) with (fillfactor = 10);
create index idx2 on t1 (a) with (Fillfactor = 10);
create index idx3 on t1 (a) with ("fillfactor" = 10);
create index idx4 on t1 (a) with ("Fillfactor" = 10);
-- ALTER INDEX — change the definition of an index
alter index idx1 set (fillfactor = 100);
alter index idx1 set (Fillfactor = 100);
alter index idx1 set ("fillfactor" = "100");
alter index idx1 set ("Fillfactor" = 100);
-- CREATE VIEW — define a new view
create view vy1 with (check_option = local) as select 1;
create view vy1 with (Check_option = Local) as select 1;
create view vy1 with ("check_option" = "local") as select 1;
create view vy1 with ("Check_option" = "Local") as select 1;
create view vy1 with (check_option = local) as select a from t1;
-- ALTER VIEW — change the definition of a view
alter view vy1 set (check_option = local);
alter view vy1 set (Check_option = local);
alter view vy1 set ("check_option" = "local");
alter view vy1 set ("check_option" = "Local");
alter view vy1 set ("Check_option" = "Local");
/*
* --------------------------------------------------------------------------
* Commands using parameters with case insensitive matches on user input
* --------------------------------------------------------------------------
*/
-- ALTER SYSTEM — change a server configuration parameter
alter system set wal_level = replica;
alter system set Wal_level = Replica;
alter system set "wal_level" = "replica";
alter system set "Wal_level" = "Replica";
-- CREATE AGGREGATE — define a new aggregate function
-- Old syntax
create aggregate agg1 (sfunc1 = int4pl, basetype = int4, stype1 = int4, initcond1 = '0', parallel = safe);
create aggregate agg2 (sfunc1 = int4pl, basetype = int4, stype1 = int4, initcond1 = '0', "parallel" = safe);
create aggregate agg3 ("sfunc1" = int4pl, "basetype" = int4, "stype1" = int4, "initcond1" = '0', "parallel" = safe);
create aggregate agg4 ("Sfunc1" = int4pl, "Basetype" = int4, "Stype1" = int4, "Initcond1" = '0', "Parallel" = safe);
-- New syntax
create aggregate agg5(float8) (stype = internal, sfunc = ordered_set_transition, finalfunc = percentile_disc_final, finalfunc_extra = true, finalfunc_modify = read_write, parallel = safe);
create aggregate agg6(float8) (Stype = internal, Sfunc = ordered_set_transition, Finalfunc = percentile_disc_final, Finalfunc_Extra = true, Finalfunc_Modify = read_write, Parallel = safe);
create aggregate agg7(float8) ("stype" = internal, "sfunc" = ordered_set_transition, "finalfunc" = percentile_disc_final, "finalfunc_extra" = true, "finalfunc_modify" = read_write, "parallel" = safe);
create aggregate agg8(float8) ("Stype" = internal, "Sfunc" = ordered_set_transition, "Finalfunc" = percentile_disc_final, "Finalfunc_extra" = true, "Finalfunc_modify" = read_write, "Parallel" = safe);
-- CREATE COLLATION — define a new collation
create collation coll1 (lc_collate = "POSIX", lc_ctype = "POSIX" );
create collation coll2 (Lc_Collate = "POSIX", Lc_Ctype = "POSIX" );
create collation coll3 ("lc_collate" = "POSIX", "lc_ctype" = "POSIX" );
create collation coll4 ("Lc_Collate" = "POSIX", "Lc_Ctype" = "POSIX" );
-- CREATE FUNCTION — define a new function
-- The VOLATILE keyword is a hardcoded defelem, while the parameters in the
-- with() clause are parsed with the IDENT rule
create function func1 (integer) returns void as $$ begin return; end; $$ language plpgsql immutable with (iscachable, isstrict);
create function func2 (integer) returns void as $$ begin return; end; $$ language plpgsql Immutable with (isCachable, isStrict);
create function func3 (integer) returns void as $$ begin return; end; $$ language plpgsql IMMUTABLE with ("isCachable", "isStrict");
create function func4 (integer) returns void as $$ begin return; end; $$ language plpgsql immutable with ("iscachable", "isstrict");
-- CREATE OPERATOR — define a new operator
CREATE OPERATOR === (leftarg = box, rightarg = box, procedure = area_equal_procedure, commutator = ===, negator = !==, restrict = area_restriction_procedure, join = area_join_procedure, hashes, merges);
CREATE OPERATOR === (Leftarg = box, Rightarg = box, Procedure = area_equal_procedure, Commutator = ===, Negator = !==, Restrict = area_restriction_procedure, Join = area_join_procedure, Hashes, Merges);
CREATE OPERATOR === ("leftarg" = box, "rightarg" = box, "procedure" = area_equal_procedure, "commutator" = ===, "negator" = !==, "restrict" = area_restriction_procedure, "join" = area_join_procedure, "hashes", "merges");
CREATE OPERATOR === ("Leftarg" = box, "Rightarg" = box, "Procedure" = area_equal_procedure, "Commutator" = ===, "Negator" = !==, "Restrict" = area_restriction_procedure, "Join" = area_join_procedure, "Hashes", "Merges");
-- ALTER OPERATOR — change the definition of an operator
ALTER OPERATOR & (bit, bit) SET (restrict = _int_contsel, join = _int_contjoinsel);
ALTER OPERATOR & (bit, bit) SET (Restrict = _int_contsel, Join = _int_contjoinsel);
ALTER OPERATOR & (bit, bit) SET ("restrict" = _int_contsel, "Join" = _int_contjoinsel);
ALTER OPERATOR & (bit, bit) SET ("Restrict" = _int_contsel, "Join" = _int_contjoinsel);
-- CREATE TYPE — define a new data type
create function int42_in(cstring) returns int42 as 'int4in' language internal strict immutable with (iscachable, isstrict);
create function int42_out(int42) returns cstring as 'int4out' language internal strict immutable;
create type int42 (internallength = 4, input = int42_in, output = int42_out, alignment = int4, default = 42, passedbyvalue);
drop type int42 cascade;
-- ---
create function int42_in(cstring) returns int42 as 'int4in' language internal strict immutable with (iscachable, isstrict);
create function int42_out(int42) returns cstring as 'int4out' language internal strict immutable;
create type int42 (Internallength = 4, Input = int42_in, Output = int42_out, Alignment = int4, Default = 42, Passedbyvalue);
drop type int42 cascade;
-- ---
create function int42_in(cstring) returns int42 as 'int4in' language internal strict immutable with (iscachable, isstrict);
create function int42_out(int42) returns cstring as 'int4out' language internal strict immutable;
create type int42 ("internallength" = 4, "input" = int42_in, "output" = int42_out, "alignment" = int4, "default" = 42, "passedbyvalue");
drop type int42 cascade;
-- ---
create function int42_in(cstring) returns int42 as 'int4in' language internal strict immutable with (iscachable, isstrict);
create function int42_out(int42) returns cstring as 'int4out' language internal strict immutable;
create type int42 ("Internallength" = 4, "Input" = int42_in, "Output" = int42_out, "Alignment" = int4, "Default" = 42, "Passedbyvalue");
drop type int42 cascade;
-- CREATE TEXT SEARCH CONFIGURATION — define a new text search configuration
create text search configuration tsconf1 (copy = english);
create text search configuration tsconf2 (Copy = english);
create text search configuration tsconf3 ("copy" = english);
create text search configuration tsconf4 ("Copy" = english);
-- CREATE TEXT SEARCH DICTIONARY — define a new text search dictionary
create text search dictionary tsdict1 (template = ispell, dictfile = ispell_sample, afffile = ispell_sample);
create text search dictionary tsdict2 (Template = ispell, DictFile = ispell_sample, AffFile = ispell_sample);
create text search dictionary tsdict3 ("template" = ispell, "dictfile" = ispell_sample, "afffile" = ispell_sample);
create text search dictionary tsdict4 ("Template" = ispell, "DictFile" = ispell_sample, "AffFile" = ispell_sample);
-- ALTER TEXT SEARCH DICTIONARY — change the definition of a text search dictionary
alter text search dictionary tsdict1 (dictfile = ispell_sample);
alter text search dictionary tsdict1 (Dictfile = ispell_sample);
alter text search dictionary tsdict1 ("dictfile" = ispell_sample);
alter text search dictionary tsdict1 ("Dictfile" = ispell_sample);
-- CREATE TEXT SEARCH PARSER — define a new text search parser
create text search parser tspars1 (start = start_function);
create text search parser tspars1 (Start = start_function);
create text search parser tspars1 ("start" = start_function);
create text search parser tspars1 ("Start" = start_function);
-- CREATE TEXT SEARCH TEMPLATE — define a new text search template
create text search template tstemp1 (init = init_function);
create text search template tstemp1 (Init = init_function);
create text search template tstemp1 ("init" = init_function);
create text search template tstemp1 ("Init" = init_function);
-- SET — change a run-time parameter
set search_path to public;
set Search_path to public;
set "search_path" to public;
set "Search_path" to public;
set search_path to "Public";
-- RESET — restore the value of a run-time parameter to the default value
reset search_path;
reset Search_path;
reset "Search_path";
-- SHOW — show the value of a run-time parameter
show geqo;
show Geqo;
show "geqo";
show "Geqo";
/*
* --------------------------------------------------------------------------
* Commands using parameters with case sensitive matches on user input
* --------------------------------------------------------------------------
*/
-- CREATE DATABASE — create a new database
create database db1 with lc_collate 'sv_SE.iso885915' lc_ctype 'sv_SE.iso885915' encoding LATIN9 template template0;
create database db2 with Lc_Collate 'sv_SE.iso885915' Lc_Ctype 'sv_SE.iso885915' Encoding LATIN9 Template template0;
create database db3 with "lc_collate" 'sv_SE.iso885915' "lc_ctype" 'sv_SE.iso885915' "encoding" LATIN9 "template" template0;
create database db4 with "Lc_Collate" 'sv_SE.iso885915' "Lc_Ctype" 'sv_SE.iso885915' "Encoding" LATIN9 "Template" template0;
-- ALTER DATABASE — change a database
alter database db1 with allow_connections = true connection limit = 10;
alter database db1 with Allow_Connections = true Connection Limit = 10;
alter database db1 with "allow_connections" = true "connection limit" = 10;
alter database db2 with "Allow_Connections" = true "Connection Limit" = 10; -- ERROR
-- CREATE EVENT TRIGGER — define a new event trigger
create function tf1() returns event_trigger as $$ begin return; end $$ language plpgsql;
create event trigger et1 on ddl_command_start when tag in ('create table', 'CREATE FUNCTION') execute procedure tf1();
create event trigger et2 on ddl_command_start when Tag in ('create table', 'CREATE FUNCTION') execute procedure tf1();
create event trigger et3 on ddl_command_start when "tag" in ('create table', 'CREATE FUNCTION') execute procedure tf1();
create event trigger et4 on ddl_command_start when "Tag" in ('create table', 'CREATE FUNCTION') execute procedure tf1(); -- ERROR
-- CREATE OPERATOR FAMILY — define a new operator family
create operator family opfam1 using btree;
create operator family opfam2 using Btree;
create operator family opfam3 using "btree";
create operator family opfam4 using "Btree"; -- ERROR
-- CREATE PUBLICATION — define a new publication
create table t (a integer);
create publication pub1 for table t with (publish = 'insert');
create publication pub2 for table t with (Publish = 'insert');
create publication pub3 for table t with ("publish" = 'insert');
create publication pub4 for table t with ("Publish" = 'insert'); -- ERROR
-- ALTER PUBLICATION — change the definition of a publication
alter publication pub1 set (publish = 'insert');
alter publication pub1 set (Publish = 'insert');
alter publication pub1 set ("publish" = 'insert');
alter publication pub1 set ("Publish" = 'insert'); -- ERROR
-- CREATE SUBSCRIPTION — define a new subscription
create subscription sub1 connection 'dbname=doesnotexist' publication testpub with (connect = false, enabled = false, synchronous_commit = off);
create subscription sub2 connection 'dbname=doesnotexist' publication testpub with (Connect = false, Enabled = false, Synchronous_commit = off);
create subscription sub3 connection 'dbname=doesnotexist' publication testpub with ("connect" = false, "enabled" = false, "synchronous_commit" = off);
create subscription sub4 connection 'dbname=doesnotexist' publication testpub with ("Connect" = false, "Enabled" = false, "Synchronous_commit" = off); -- ERROR
-- ALTER SUBSCRIPTION — change the definition of a subscription
alter subscription sub1 set (synchronous_commit = off);
alter subscription sub1 set (Synchronous_commit = off);
alter subscription sub1 set ("synchronous_commit" = off);
alter subscription sub1 set ("Synchronous_commit" = off); -- ERROR
-- CREATE STATISTICS — define extended statistics
-- The statistics_kind parameter is case sensitive, but it's not actually a
-- defelem but is parsed with the name_list rule coming via via makeString()
create table tt (a integer, b integer);
create statistics stat1 (dependencies) on a,b from tt;
create statistics stat2 (Dependencies) on a,b from tt;
create statistics stat3 ("dependencies") on a,b from tt;
create statistics stat4 ("Dependencies") on a,b from tt; -- ERROR
-- COPY — copy data between a file and a table
copy tt to '/tmp/tt' with (format csv);
copy tt to '/tmp/tt' with (Format csv);
copy tt to '/tmp/tt' with ("format" csv);
copy tt to '/tmp/tt' with ("Format" csv); -- ERROR
-- EXPLAIN — show the execution plan of a statement
explain (analyze, buffers) select 1;
explain (analyze, Buffers) select 1;
explain (analyze, "buffers") select 1;
explain (analyze, "Buffers") select 1; -- ERROR
-- SET SESSION AUTHORIZATION — set the session user identifier and the current user identifier of the current session
create user myuser;
set session authorization myuser;
set session authorization Myuser;
set session authorization "myuser";
set session authorization "Myuser"; -- ERROR
/*
* --------------------------------------------------------------------------
* Commands using parameters with case sensitive matches on hardcoded defelem
* entries from the parser
* --------------------------------------------------------------------------
*/
-- ALTER DEFAULT PRIVILEGES — define default access privileges
-- ALTER FUNCTION — change the definition of a function
-- ALTER GROUP — change role name or membership
-- ALTER PROCEDURE — change the definition of a procedure
-- ALTER ROLE — change a database role
-- ALTER ROUTINE — change the definition of a routine
-- ALTER SEQUENCE — change the definition of a sequence generator
-- ALTER USER — change a database role
-- BEGIN — start a transaction block
-- CREATE EXTENSION — install an extension
-- CREATE GROUP — define a new database role
-- CREATE ROLE — define a new database role
-- CREATE SEQUENCE — define a new sequence generator
-- CREATE USER — define a new database role
-- SET TRANSACTION — set the characteristics of the current transaction
-- START TRANSACTION — start a transaction block
/*
* --------------------------------------------------------------------------
* DML commands not considered in this patch.
* --------------------------------------------------------------------------
*/
-- INSERT — create new rows in a table
-- SELECT — retrieve rows from a table or view
-- SELECT INTO — define a new table from the results of a query
-- UPDATE — update rows of a table
/*
* --------------------------------------------------------------------------
* SQL/MED commands which perform where matching against the options defined
* is performed in the FDW extension, postgres_fdw is performing case sensitive
* matching but each external module is free to do whichever. 'handler' and
* 'validator' are hardcoded defelem parameters handled by the foreign command
* code.
* --------------------------------------------------------------------------
*/
create extension postgres_fdw;
-- CREATE FOREIGN DATA WRAPPER — define a new foreign-data wrapper
-- ALTER FOREIGN DATA WRAPPER — change the definition of a foreign-data wrapper
-- ALTER FOREIGN TABLE — change the definition of a foreign table
-- ALTER USER MAPPING — change the definition of a user mapping
-- CREATE SERVER — define a new foreign server
CREATE SERVER fs1 FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'foo', dbname 'foodb', port '5432');
CREATE SERVER fs2 FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'foo', DBname 'foodb', port '5432');
CREATE SERVER fs3 FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'foo', "dbname" 'foodb', port '5432');
CREATE SERVER fs4 FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'foo', "DBname" 'foodb', port '5432');
-- ALTER SERVER — change the definition of a foreign server
-- CREATE USER MAPPING — define a new mapping of a user to a foreign server
-- DROP USER MAPPING — remove a user mapping for a foreign server
-- IMPORT FOREIGN SCHEMA — import table definitions from a foreign server