diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 71e20f2740..fbf78e602d 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -1813,7 +1813,8 @@ SCRAM-SHA-256$<iteration count>:&l
m = materialized view,
c = composite type,
f = foreign table,
- p = partitioned table
+ p = partitioned table,
+ V = schema variable
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 640ff09a7b..8e652e01cc 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -15743,6 +15743,83 @@ SELECT * FROM pg_ls_dir('.') WITH ORDINALITY AS t(ls,n);
+
+ Schema Variable Functions
+
+
+ Schema Variable Functions
+ functions
+
+
+
+ get_schema_variable
+
+
+
+ set_schema_variable
+
+
+
+ These functions allow reading and writing schema variables values.
+ If the schema variable referenced does not exist (created using )
+ these functions will (do something...).
+
+
+
+ Functions for access to schema variables
+
+
+
+ Function
+ Argument Type
+ Return Type
+ Description
+
+
+
+
+ get_schema_variable(variable, expected type)
+ regclass, anyelement
+ anyelement
+
+ Returns value of schema variable converted to expected type.
+
+
+
+
+ set_schema_variable(variable, value)
+ regclass, anyelement
+ void
+
+ Sets the value of schema variable to value, after converting the input to the correct type.
+
+
+
+
+
+
+
+ An example:
+
+
+CREATE TEMP VARIABLE foo AS numeric;
+SELECT set_schema_variable('foo', 345.445);
+ set_schema_variable
+---------------------
+
+(1 row)
+
+SELECT get_schema_variable('foo', null::numeric);
+
+ get_schema_variable
+---------------------
+ 345.445
+(1 row)
+
+
+
+
+
System Information Functions
diff --git a/doc/src/sgml/ref/allfiles.sgml b/doc/src/sgml/ref/allfiles.sgml
index 22e6893211..1d34f72bdd 100644
--- a/doc/src/sgml/ref/allfiles.sgml
+++ b/doc/src/sgml/ref/allfiles.sgml
@@ -99,6 +99,7 @@ Complete list of usable sgml source files in this directory.
+
@@ -147,6 +148,7 @@ Complete list of usable sgml source files in this directory.
+
@@ -155,6 +157,7 @@ Complete list of usable sgml source files in this directory.
+
diff --git a/doc/src/sgml/ref/create_variable.sgml b/doc/src/sgml/ref/create_variable.sgml
new file mode 100644
index 0000000000..0205e4365a
--- /dev/null
+++ b/doc/src/sgml/ref/create_variable.sgml
@@ -0,0 +1,149 @@
+
+
+
+
+ CREATE VARIABLE
+
+
+
+ CREATE VARIABLE
+ 7
+ SQL - Language Statements
+
+
+
+ CREATE VARIABLE
+ define a new permissioned typed schema variable
+
+
+
+
+CREATE VARIABLE [ IF NOT EXISTS ] name [ AS ] data_type ]
+
+
+
+ Description
+
+
+ CREATE VARIABLE creates a new schema variable.
+ These variables are scalar typed, non-transactional, and, like relations,
+ exist within a schema with access controlled via
+ GRANT and REVOKE.
+
+
+
+ The value of a schema variable is session-local. Retrieving
+ a variable's value will return NULL unless its value has been set
+ to something else in the current session.
+
+
+
+ Retrieval is done via the get_schema_variabledunxrion or the SQL
+ command SELECT. Setting of values is done via the
+ set_schema_variable function or the SQL command
+ LET.
+ Notably, while schema variables are in many ways a kind of table you cannot use
+ UPDATE on them.
+
+
+
+ For purposes of name uniqueness relation-like objects (e.g., tables, indexes)
+ within the same schema are considered. i.e., you cannot give a table and a
+ schema variable the same name. This is a consequence of them being treated
+ like relations for purposes of SELECT.
+
+
+
+
+ Parameters
+
+
+
+ IF NOT EXISTS
+
+
+ Do not throw an error if the name already exists. A notice is issued in this case.
+ Note that type of the variable is not considered, nor could it be since the namespace
+ searched contains non-variable objects.
+
+
+
+
+
+ name
+
+
+ The name (optionally schema-qualified) of the variable to be created.
+
+
+
+
+
+ data_type
+
+
+ The name (optionally schema-qualified) of the data type of the variable to be created.
+
+
+
+
+
+
+
+ Notes
+
+
+ Use DROP VARIABLE to remove a variable.
+
+
+
+
+ Examples
+
+
+ Create an integer variable var1:
+
+CREATE VARIABLE var1 AS integer;
+
+
+
+
+ Set this variable's value; then retrieve it converted to numeric.
+
+CREATE VARIABLE
+postgres=# select set_schema_variable('var1', 10);
+ set_schema_variable
+---------------------
+
+(1 row)
+
+postgres=# select get_schema_variable('var1', null::numeric);
+ get_schema_variable
+---------------------
+ 10
+(1 row)
+
+
+
+
+
+ Compatibility
+
+
+ CREATE VARIABLE is a PostgreSQL feature.
+
+
+
+
+
+ See Also
+
+
+
+
+
+
+
diff --git a/doc/src/sgml/ref/discard.sgml b/doc/src/sgml/ref/discard.sgml
index 6b909b7232..395453bba0 100644
--- a/doc/src/sgml/ref/discard.sgml
+++ b/doc/src/sgml/ref/discard.sgml
@@ -21,7 +21,7 @@ PostgreSQL documentation
-DISCARD { ALL | PLANS | SEQUENCES | TEMPORARY | TEMP }
+DISCARD { ALL | PLANS | SEQUENCES | TEMPORARY | TEMP | VARIABLES}
@@ -75,6 +75,16 @@ DISCARD { ALL | PLANS | SEQUENCES | TEMPORARY | TEMP }
+
+ VARIABLES
+
+
+ Sets the value of all schema variables to NULL.
+
+
+
+
+
ALL
diff --git a/doc/src/sgml/ref/drop_variable.sgml b/doc/src/sgml/ref/drop_variable.sgml
new file mode 100644
index 0000000000..06130fd510
--- /dev/null
+++ b/doc/src/sgml/ref/drop_variable.sgml
@@ -0,0 +1,92 @@
+
+
+
+
+ DROP VARIABLE
+
+
+
+ DROP VARIABLE
+ 7
+ SQL - Language Statements
+
+
+
+ DROP VARIABLE
+ remove a schema variable
+
+
+
+
+DROP VARIABLE [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]
+
+
+
+
+ Description
+
+
+ DROP VARIABLE removes a schema variable.
+ A variable can only be dropped by its owner or a superuser.
+
+
+
+
+
+ Parameters
+
+
+
+ IF EXISTS
+
+
+ Do not throw an error if the variable does not exist. A notice is issued
+ in this case.
+
+
+
+
+
+ name
+
+
+ The name (optionally schema-qualified) of a schema variable.
+
+
+
+
+
+
+
+ Examples
+
+
+ To remove the schema variable var1:
+
+
+DROP VARIABLE var1;
+
+
+
+
+ Compatibility
+
+
+ DROP VARIABLE is proprietary PostgreSQL command.
+
+
+
+
+
+ See Also
+
+
+
+
+
+
+
diff --git a/doc/src/sgml/ref/grant.sgml b/doc/src/sgml/ref/grant.sgml
index ff64c7a3ba..006364ebe5 100644
--- a/doc/src/sgml/ref/grant.sgml
+++ b/doc/src/sgml/ref/grant.sgml
@@ -79,6 +79,12 @@ GRANT { USAGE | ALL [ PRIVILEGES ] }
ON TYPE type_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
+GRANT { { SELECT | UPDATE }
+ [, ...] | ALL [ PRIVILEGES ] }
+ ON { VARIABLE variable_name [, ...]
+ | ALL VARIABLES IN SCHEMA schema_name [, ...] }
+ TO role_specification [, ...] [ WITH GRANT OPTION ]
+
where role_specification can be:
[ GROUP ] role_name
@@ -167,6 +173,7 @@ GRANT role_name [, ...] TO PUBLIC are as follows:
@@ -204,6 +211,8 @@ GRANT role_name [, ...] TO currval function.
For large objects, this privilege allows the object to be read.
+ For schema variables, this privilege allows the get_schema_variable
+ to read the variable's value.
@@ -239,6 +248,9 @@ GRANT role_name [, ...] TO setval functions.
For large objects, this privilege allows writing or truncating the
object.
+ For schema variables, this privilege allows LET
+ and set_schema_variable to modify the schema variable's
+ value.
diff --git a/doc/src/sgml/ref/let.sgml b/doc/src/sgml/ref/let.sgml
new file mode 100644
index 0000000000..e8bf3f6dd4
--- /dev/null
+++ b/doc/src/sgml/ref/let.sgml
@@ -0,0 +1,90 @@
+
+
+
+
+ LET
+
+
+
+ LET
+ 7
+ SQL - Language Statements
+
+
+
+ LET
+ change a schema variable's value
+
+
+
+
+LET schema_variable = sql_expression
+
+
+
+
+ Description
+
+
+ The LET command updates the specified schema variable' value.
+
+
+
+
+
+ Parameters
+
+
+
+ schema_variable
+
+
+ The name of schema variable.
+
+
+
+
+
+ sql expression
+
+
+ An SQL expression, the result is cast to the schema variable's type.
+
+
+
+
+
+
+ Example:
+
+CREATE VARIABLE myvar AS integer;
+LET myvar = 10;
+LET myvar = (SELECT sum(val) FROM tab);
+
+
+
+
+
+ Compatibility
+
+
+
+ LET extends syntax defined in the SQL
+ standard. The standard knows SET command,
+ that is used for different purpouse in PostgreSQL.
+
+
+
+
+ See Also
+
+
+
+
+
+
+
diff --git a/doc/src/sgml/ref/revoke.sgml b/doc/src/sgml/ref/revoke.sgml
index 7018202f14..73778f01f9 100644
--- a/doc/src/sgml/ref/revoke.sgml
+++ b/doc/src/sgml/ref/revoke.sgml
@@ -108,6 +108,14 @@ REVOKE [ GRANT OPTION FOR ]
REVOKE [ ADMIN OPTION FOR ]
role_name [, ...] FROM role_name [, ...]
[ CASCADE | RESTRICT ]
+
+REVOKE [ GRANT OPTION FOR ]
+ { { SELECT | UPDATE }
+ [, ...] | ALL [ PRIVILEGES ] }
+ ON { VARIABLE variable_name [, ...]
+ | ALL VARIABLES IN SCHEMA schema_name [, ...] }
+ FROM { [ GROUP ] role_name | PUBLIC } [, ...]
+ [ CASCADE | RESTRICT ]
diff --git a/doc/src/sgml/reference.sgml b/doc/src/sgml/reference.sgml
index d27fb414f7..b3f9fff511 100644
--- a/doc/src/sgml/reference.sgml
+++ b/doc/src/sgml/reference.sgml
@@ -127,6 +127,7 @@
&createType;
&createUser;
&createUserMapping;
+ &createVariable;
&createView;
&deallocate;
&declare;
@@ -175,6 +176,7 @@
&dropType;
&dropUser;
&dropUserMapping;
+ &dropVariable;
&dropView;
&end;
&execute;
@@ -183,6 +185,7 @@
&grant;
&importForeignSchema;
&insert;
+ &let;
&listen;
&load;
&lock;
diff --git a/src/backend/catalog/aclchk.c b/src/backend/catalog/aclchk.c
index 1156627b9e..268534ea87 100644
--- a/src/backend/catalog/aclchk.c
+++ b/src/backend/catalog/aclchk.c
@@ -284,6 +284,9 @@ restrict_and_check_grant(bool is_grant, AclMode avail_goptions, bool all_privs,
case OBJECT_TYPE:
whole_mask = ACL_ALL_RIGHTS_TYPE;
break;
+ case OBJECT_VARIABLE:
+ whole_mask = ACL_ALL_RIGHTS_VARIABLE;
+ break;
default:
elog(ERROR, "unrecognized object type: %d", objtype);
/* not reached, but keep compiler quiet */
@@ -506,6 +509,10 @@ ExecuteGrantStmt(GrantStmt *stmt)
all_privileges = ACL_ALL_RIGHTS_FOREIGN_SERVER;
errormsg = gettext_noop("invalid privilege type %s for foreign server");
break;
+ case OBJECT_VARIABLE:
+ all_privileges = ACL_ALL_RIGHTS_VARIABLE;
+ errormsg = gettext_noop("invalid privilege type %s for schema variable");
+ break;
default:
elog(ERROR, "unrecognized GrantStmt.objtype: %d",
(int) stmt->objtype);
@@ -576,6 +583,7 @@ ExecGrantStmt_oids(InternalGrant *istmt)
{
case OBJECT_TABLE:
case OBJECT_SEQUENCE:
+ case OBJECT_VARIABLE:
ExecGrant_Relation(istmt);
break;
case OBJECT_DATABASE:
@@ -645,6 +653,7 @@ objectNamesToOids(ObjectType objtype, List *objnames)
{
case OBJECT_TABLE:
case OBJECT_SEQUENCE:
+ case OBJECT_VARIABLE:
foreach(cell, objnames)
{
RangeVar *relvar = (RangeVar *) lfirst(cell);
@@ -1021,6 +1030,10 @@ ExecAlterDefaultPrivilegesStmt(ParseState *pstate, AlterDefaultPrivilegesStmt *s
all_privileges = ACL_ALL_RIGHTS_SCHEMA;
errormsg = gettext_noop("invalid privilege type %s for schema");
break;
+ case OBJECT_VARIABLE:
+ all_privileges = ACL_ALL_RIGHTS_VARIABLE;
+ errormsg = gettext_noop("invalid privilege type %s for schema variable");
+ break;
default:
elog(ERROR, "unrecognized GrantStmt.objtype: %d",
(int) action->objtype);
@@ -1218,6 +1231,12 @@ SetDefaultACL(InternalDefaultACL *iacls)
this_privileges = ACL_ALL_RIGHTS_SCHEMA;
break;
+ case OBJECT_VARIABLE:
+ objtype = DEFACLOBJ_VARIABLE;
+ if (iacls->all_privs && this_privileges == ACL_NO_RIGHTS)
+ this_privileges = ACL_ALL_RIGHTS_VARIABLE;
+ break;
+
default:
elog(ERROR, "unrecognized objtype: %d",
(int) iacls->objtype);
@@ -1444,6 +1463,9 @@ RemoveRoleFromObjectACL(Oid roleid, Oid classid, Oid objid)
case DEFACLOBJ_NAMESPACE:
iacls.objtype = OBJECT_SCHEMA;
break;
+ case DEFACLOBJ_VARIABLE:
+ iacls.objtype = OBJECT_VARIABLE;
+ break;
default:
/* Shouldn't get here */
elog(ERROR, "unexpected default ACL type: %d",
@@ -3459,6 +3481,9 @@ aclcheck_error(AclResult aclerr, ObjectType objtype,
case OBJECT_TYPE:
msg = gettext_noop("permission denied for type %s");
break;
+ case OBJECT_VARIABLE:
+ msg = gettext_noop("permission denied for schema variable %s");
+ break;
case OBJECT_VIEW:
msg = gettext_noop("permission denied for view %s");
break;
@@ -3569,6 +3594,9 @@ aclcheck_error(AclResult aclerr, ObjectType objtype,
case OBJECT_TYPE:
msg = gettext_noop("must be owner of type %s");
break;
+ case OBJECT_VARIABLE:
+ msg = gettext_noop("must be owner of schema variable %s");
+ break;
case OBJECT_VIEW:
msg = gettext_noop("must be owner of view %s");
break;
@@ -3683,6 +3711,7 @@ pg_aclmask(ObjectType objtype, Oid table_oid, AttrNumber attnum, Oid roleid,
pg_attribute_aclmask(table_oid, attnum, roleid, mask, how);
case OBJECT_TABLE:
case OBJECT_SEQUENCE:
+ case OBJECT_VARIABLE:
return pg_class_aclmask(table_oid, roleid, mask, how);
case OBJECT_DATABASE:
return pg_database_aclmask(table_oid, roleid, mask, how);
diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c
index 0f34f5381a..558e641d56 100644
--- a/src/backend/catalog/heap.c
+++ b/src/backend/catalog/heap.c
@@ -291,6 +291,7 @@ heap_create(const char *relname,
switch (relkind)
{
case RELKIND_VIEW:
+ case RELKIND_VARIABLE:
case RELKIND_COMPOSITE_TYPE:
case RELKIND_FOREIGN_TABLE:
case RELKIND_PARTITIONED_TABLE:
@@ -1067,7 +1068,9 @@ heap_create_with_catalog(const char *relname,
if (existing_relid != InvalidOid)
ereport(ERROR,
(errcode(ERRCODE_DUPLICATE_TABLE),
- errmsg("relation \"%s\" already exists", relname)));
+ errmsg("%s \"%s\" already exists",
+ relkind == RELKIND_VARIABLE ? "variable" : "relation",
+ relname)));
/*
* Since we are going to create a rowtype as well, also check for
@@ -1150,6 +1153,10 @@ heap_create_with_catalog(const char *relname,
relacl = get_user_default_acl(OBJECT_SEQUENCE, ownerid,
relnamespace);
break;
+ case RELKIND_VARIABLE:
+ relacl = get_user_default_acl(OBJECT_VARIABLE, ownerid,
+ relnamespace);
+ break;
default:
relacl = NULL;
break;
@@ -1181,7 +1188,8 @@ heap_create_with_catalog(const char *relname,
* Decide whether to create an array type over the relation's rowtype. We
* do not create any array types for system catalogs (ie, those made
* during initdb). We do not create them where the use of a relation as
- * such is an implementation detail: toast tables, sequences and indexes.
+ * such is an implementation detail: toast tables, sequences, indexes and
+ * variables.
*/
if (IsUnderPostmaster && (relkind == RELKIND_RELATION ||
relkind == RELKIND_VIEW ||
diff --git a/src/backend/catalog/objectaddress.c b/src/backend/catalog/objectaddress.c
index 570e65affb..62479743c7 100644
--- a/src/backend/catalog/objectaddress.c
+++ b/src/backend/catalog/objectaddress.c
@@ -520,6 +520,9 @@ static const struct object_type_map
{
"sequence", OBJECT_SEQUENCE
},
+ {
+ "variable", OBJECT_VARIABLE
+ },
{
"toast table", -1
}, /* unmapped */
@@ -824,6 +827,7 @@ get_object_address(ObjectType objtype, Node *object,
case OBJECT_VIEW:
case OBJECT_MATVIEW:
case OBJECT_FOREIGN_TABLE:
+ case OBJECT_VARIABLE:
address =
get_relation_by_qualified_name(objtype, castNode(List, object),
&relation, lockmode,
@@ -1260,6 +1264,14 @@ get_relation_by_qualified_name(ObjectType objtype, List *object,
errmsg("\"%s\" is not a foreign table",
RelationGetRelationName(relation))));
break;
+ case OBJECT_VARIABLE:
+ if (relation->rd_rel->relkind != RELKIND_VARIABLE)
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("\"%s\" is not a schema variable",
+ RelationGetRelationName(relation))));
+ break;
+
default:
elog(ERROR, "unrecognized objtype: %d", (int) objtype);
break;
@@ -1847,6 +1859,8 @@ get_object_address_defacl(List *object, bool missing_ok)
case DEFACLOBJ_NAMESPACE:
objtype_str = "schemas";
break;
+ case DEFACLOBJ_VARIABLE:
+ objtype_str = "variables";
default:
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
@@ -2109,6 +2123,7 @@ pg_get_object_address(PG_FUNCTION_ARGS)
{
case OBJECT_TABLE:
case OBJECT_SEQUENCE:
+ case OBJECT_VARIABLE:
case OBJECT_VIEW:
case OBJECT_MATVIEW:
case OBJECT_INDEX:
@@ -2233,6 +2248,7 @@ check_object_ownership(Oid roleid, ObjectType objtype, ObjectAddress address,
case OBJECT_INDEX:
case OBJECT_SEQUENCE:
case OBJECT_TABLE:
+ case OBJECT_VARIABLE:
case OBJECT_VIEW:
case OBJECT_MATVIEW:
case OBJECT_FOREIGN_TABLE:
@@ -3299,6 +3315,11 @@ getObjectDescription(const ObjectAddress *object)
_("default privileges on new schemas belonging to role %s"),
GetUserNameFromId(defacl->defaclrole, false));
break;
+ case DEFACLOBJ_VARIABLE:
+ appendStringInfo(&buffer,
+ _("default privileges on new schema variables belonging to role %s"),
+ GetUserNameFromId(defacl->defaclrole, false));
+ break;
default:
/* shouldn't get here */
appendStringInfo(&buffer,
@@ -3502,6 +3523,10 @@ getRelationDescription(StringInfo buffer, Oid relid)
appendStringInfo(buffer, _("sequence %s"),
relname);
break;
+ case RELKIND_VARIABLE:
+ appendStringInfo(buffer, _("variable %s"),
+ relname);
+ break;
case RELKIND_TOASTVALUE:
appendStringInfo(buffer, _("toast table %s"),
relname);
@@ -4830,6 +4855,10 @@ getObjectIdentityParts(const ObjectAddress *object,
appendStringInfoString(&buffer,
" on schemas");
break;
+ case DEFACLOBJ_VARIABLE:
+ appendStringInfoString(&buffer,
+ " on schema variables");
+ break;
}
if (objname)
@@ -5122,6 +5151,8 @@ get_relkind_objtype(char relkind)
return OBJECT_INDEX;
case RELKIND_SEQUENCE:
return OBJECT_SEQUENCE;
+ case RELKIND_VARIABLE:
+ return OBJECT_VARIABLE;
case RELKIND_VIEW:
return OBJECT_VIEW;
case RELKIND_MATVIEW:
diff --git a/src/backend/commands/Makefile b/src/backend/commands/Makefile
index 4a6c99e090..5747272c9a 100644
--- a/src/backend/commands/Makefile
+++ b/src/backend/commands/Makefile
@@ -18,7 +18,7 @@ OBJS = amcmds.o aggregatecmds.o alter.o analyze.o async.o cluster.o comment.o \
event_trigger.o explain.o extension.o foreigncmds.o functioncmds.o \
indexcmds.o lockcmds.o matview.o operatorcmds.o opclasscmds.o \
policy.o portalcmds.o prepare.o proclang.o publicationcmds.o \
- schemacmds.o seclabel.o sequence.o statscmds.o subscriptioncmds.o \
+ schemacmds.o schemavar.o seclabel.o sequence.o statscmds.o subscriptioncmds.o \
tablecmds.o tablespace.o trigger.o tsearchcmds.o typecmds.o user.o \
vacuum.o vacuumlazy.o variable.o view.o
diff --git a/src/backend/commands/alter.c b/src/backend/commands/alter.c
index 0d63866fb0..e174e240b4 100644
--- a/src/backend/commands/alter.c
+++ b/src/backend/commands/alter.c
@@ -348,6 +348,7 @@ ExecRenameStmt(RenameStmt *stmt)
case OBJECT_MATVIEW:
case OBJECT_INDEX:
case OBJECT_FOREIGN_TABLE:
+ case OBJECT_VARIABLE:
return RenameRelation(stmt);
case OBJECT_COLUMN:
@@ -478,6 +479,7 @@ ExecAlterObjectSchemaStmt(AlterObjectSchemaStmt *stmt,
case OBJECT_TABLE:
case OBJECT_VIEW:
case OBJECT_MATVIEW:
+ case OBJECT_VARIABLE:
address = AlterTableNamespace(stmt,
oldSchemaAddr ? &oldNspOid : NULL);
break;
diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c
index b3933df9af..71e5aad852 100644
--- a/src/backend/commands/copy.c
+++ b/src/backend/commands/copy.c
@@ -1484,6 +1484,9 @@ BeginCopy(ParseState *pstate,
Assert(query->utilityStmt == NULL);
+ /* Don't expect LET stmt here, is not possible to do write it */
+ Assert(query->commandType != CMD_LET);
+
/*
* Similarly the grammar doesn't enforce the presence of a RETURNING
* clause, but this is required here.
diff --git a/src/backend/commands/discard.c b/src/backend/commands/discard.c
index 353ec990af..33db47e634 100644
--- a/src/backend/commands/discard.c
+++ b/src/backend/commands/discard.c
@@ -18,6 +18,7 @@
#include "commands/async.h"
#include "commands/discard.h"
#include "commands/prepare.h"
+#include "commands/schemavar.h"
#include "commands/sequence.h"
#include "utils/guc.h"
#include "utils/portal.h"
@@ -25,7 +26,7 @@
static void DiscardAll(bool isTopLevel);
/*
- * DISCARD { ALL | SEQUENCES | TEMP | PLANS }
+ * DISCARD { ALL | SEQUENCES | TEMP | PLANS | VARIABLES}
*/
void
DiscardCommand(DiscardStmt *stmt, bool isTopLevel)
@@ -48,6 +49,10 @@ DiscardCommand(DiscardStmt *stmt, bool isTopLevel)
ResetTempTableNamespace();
break;
+ case DISCARD_VARIABLES:
+ ResetSchemaVariablesCache();
+ break;
+
default:
elog(ERROR, "unrecognized DISCARD target: %d", stmt->target);
}
@@ -75,4 +80,5 @@ DiscardAll(bool isTopLevel)
ResetPlanCache();
ResetTempTableNamespace();
ResetSequenceCaches();
+ ResetSchemaVariablesCache();
}
diff --git a/src/backend/commands/event_trigger.c b/src/backend/commands/event_trigger.c
index 549c7ea51d..c8e2b822e1 100644
--- a/src/backend/commands/event_trigger.c
+++ b/src/backend/commands/event_trigger.c
@@ -126,6 +126,7 @@ static event_trigger_support_data event_trigger_support[] = {
{"TEXT SEARCH TEMPLATE", true},
{"TYPE", true},
{"USER MAPPING", true},
+ {"VARIABLE", true},
{"VIEW", true},
{NULL, false}
};
@@ -1124,6 +1125,7 @@ EventTriggerSupportsObjectType(ObjectType obtype)
case OBJECT_TSTEMPLATE:
case OBJECT_TYPE:
case OBJECT_USER_MAPPING:
+ case OBJECT_VARIABLE:
case OBJECT_VIEW:
return true;
@@ -2222,6 +2224,8 @@ stringify_grant_objtype(ObjectType objtype)
return "TABLESPACE";
case OBJECT_TYPE:
return "TYPE";
+ case OBJECT_VARIABLE:
+ return "VARIABLE";
/* these currently aren't used */
case OBJECT_ACCESS_METHOD:
case OBJECT_AGGREGATE:
@@ -2304,6 +2308,8 @@ stringify_adefprivs_objtype(ObjectType objtype)
return "TABLESPACES";
case OBJECT_TYPE:
return "TYPES";
+ case OBJECT_VARIABLE:
+ return "VARIABLES";
/* these currently aren't used */
case OBJECT_ACCESS_METHOD:
case OBJECT_AGGREGATE:
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index 41cd47e8bc..11c8257fca 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -893,6 +893,9 @@ ExplainNode(PlanState *planstate, List *ancestors,
case CMD_DELETE:
pname = operation = "Delete";
break;
+ case CMD_LET:
+ pname = operation = "Let";
+ break;
default:
pname = "???";
break;
diff --git a/src/backend/commands/prepare.c b/src/backend/commands/prepare.c
index b945b1556a..a69471e926 100644
--- a/src/backend/commands/prepare.c
+++ b/src/backend/commands/prepare.c
@@ -151,6 +151,7 @@ PrepareQuery(PrepareStmt *stmt, const char *queryString,
case CMD_INSERT:
case CMD_UPDATE:
case CMD_DELETE:
+ case CMD_LET:
/* OK */
break;
default:
diff --git a/src/backend/commands/schemavar.c b/src/backend/commands/schemavar.c
new file mode 100644
index 0000000000..90e1d3e457
--- /dev/null
+++ b/src/backend/commands/schemavar.c
@@ -0,0 +1,676 @@
+/*-------------------------------------------------------------------------
+ *
+ * schemavar.c
+ * PostgreSQL session variable support code.
+ *
+ * Portions Copyright (c) 1996-2018, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994, Regents of the University of California
+ *
+ *
+ * IDENTIFICATION
+ * src/backend/commands/schemavar.c
+ *
+ *-------------------------------------------------------------------------
+ */
+#include "postgres.h"
+#include "miscadmin.h"
+
+#include "access/htup_details.h"
+#include "access/xact.h"
+#include "catalog/objectaddress.h"
+#include "catalog/namespace.h"
+#include "catalog/pg_class.h"
+#include "catalog/pg_type.h"
+#include "commands/tablecmds.h"
+#include "commands/schemavar.h"
+#include "parser/parse_coerce.h"
+#include "parser/parse_type.h"
+#include "utils/acl.h"
+#include "utils/builtins.h"
+#include "utils/datum.h"
+#include "utils/hsearch.h"
+#include "utils/inval.h"
+#include "utils/lsyscache.h"
+#include "utils/memutils.h"
+#include "utils/syscache.h"
+
+/*
+ * This schema variable cache mixes the cache and storages behave. That is not
+ * correct and it is problematic, when variable is removed. The own storage
+ * based on storage manager can be implemented, RelFileNode can be defined and
+ * mechanism based on PendingRelDelete struct can be used. This is a argument
+ * for implementation schema variables based on pg_class.
+ * Alternative solution can be detection of schema changes and recheck at and
+ * of transaction.
+ */
+typedef struct SchemaVarData
+{
+ Oid varid; /* pg_class OID of this sequence (hash key) */
+ Oid typid; /* OID of the data type */
+ int32 typmod;
+ int16 typlen;
+ bool typbyval;
+ bool isnull;
+ bool freeval;
+ Datum value;
+} SchemaVarData;
+
+typedef SchemaVarData *SchemaVar;
+
+static HTAB *schemavarhashtab = NULL; /* hash table for session variables */
+static MemoryContext SchemaVarMemoryContext = NULL;
+
+static Datum datumCast(Datum value,
+ Oid target_typid, int target_typmod,
+ Oid source_typid, int source_typmod);
+
+static bool first_time = true;
+static bool cache_is_valid = true;
+
+static void InvalidateSchemaVarCacheCallback(Datum arg, int cacheid, uint32 hashvalue);
+
+/* just mark cache to recheck */
+static void
+InvalidateSchemaVarCacheCallback(Datum arg, int cacheid, uint32 hashvalue)
+{
+ /*
+ * because this cache holds values of schema variables, then
+ * the content cannot be removed in this momemt. We should to
+ * wait on transaction end.
+ */
+ cache_is_valid = false;
+}
+
+/*
+ * Wait on commit or rollback and clean values that miss entry in system
+ * catalog. It is temporary solution (although it is working). Storage manager
+ * based solution will be better, but it is not necessary for this PoC.
+ *
+ * removes uncommitted or dropped schema variables, so event can be ignored.
+ */
+static void
+recheck_schema_variables(XactEvent event, void *arg)
+{
+ HASH_SEQ_STATUS status;
+ SchemaVar var;
+
+ if (cache_is_valid || schemavarhashtab == NULL || !IsTransactionState())
+ return;
+
+ hash_seq_init(&status, schemavarhashtab);
+
+ while ((var = (SchemaVar) hash_seq_search(&status)) != NULL)
+ {
+ HeapTuple tp = InvalidOid;
+
+ tp = SearchSysCache1(RELOID, ObjectIdGetDatum(var->varid));
+ if (!HeapTupleIsValid(tp))
+ {
+ elog(DEBUG1, "variable %d is removed from cache", var->varid);
+
+ if (var->freeval)
+ {
+ pfree(DatumGetPointer(var->value));
+ var->freeval = false;
+ }
+
+ if (hash_search(schemavarhashtab,
+ (void *) &var->varid,
+ HASH_REMOVE,
+ NULL) == NULL)
+ elog(ERROR, "hash table corrupted");
+ }
+ else
+ ReleaseSysCache(tp);
+ }
+ cache_is_valid = true;
+}
+
+/*
+ * DefineSessionVariable
+ * Creates a new variable related relation
+ */
+ObjectAddress
+DefineSchemaVariable(ParseState *pstate, CreateSchemaVarStmt *var)
+{
+ CreateStmt *stmt = makeNode(CreateStmt);
+ Oid typoid;
+ Oid varoid;
+ ObjectAddress address;
+
+ /*
+ * If if_not_exists was given and a relation with the same name already
+ * exists, bail out. (Note: we needn't check this when not if_not_exists,
+ * because DefineRelation will complain anyway.)
+ */
+ if (var->if_not_exists)
+ {
+ RangeVarGetAndCheckCreationNamespace(var->variable, NoLock, &varoid);
+ if (OidIsValid(varoid))
+ {
+ ereport(NOTICE,
+ (errcode(ERRCODE_DUPLICATE_TABLE),
+ errmsg("variable \"%s\" already exists, skipping",
+ var->variable->relname)));
+ return InvalidObjectAddress;
+ }
+ }
+
+ typoid = LookupTypeNameOid(pstate, var->typeName, false);
+
+ /*
+ * Don't allow composite types and arrays. The left expression of
+ * LET statement is simple in this moment (don't allow record field
+ * or array field specification). Without this support we should
+ * not to support non scalars ever.
+ */
+ if (type_is_rowtype(typoid))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("Composite types are not allowed as variable type.")));
+
+ if (get_base_element_type(typoid) != InvalidOid)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("Schema variables cannot be a array.")));
+
+ if (get_typtype(typoid) == TYPTYPE_PSEUDO)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATATYPE_MISMATCH),
+ errmsg("variable cannot be %s",
+ format_type_be(varoid))));
+
+ stmt->tableElts = NIL;
+ stmt->relation = var->variable;
+ stmt->inhRelations = NIL;
+ stmt->constraints = NIL;
+ stmt->options = NIL;
+ stmt->oncommit = ONCOMMIT_NOOP;
+ stmt->tablespacename = NULL;
+ stmt->if_not_exists = var->if_not_exists;
+
+ /*
+ * Use reloftype attribute. This attribute should be composite type for
+ * tables, but there are no reason to apply this rule for variables. Can
+ * be changed later with composite type support. In this moment I don't
+ * play with it, because I would not allow queries like:
+ * SELECT schemavar FROM schemavar, because there is semantic colission
+ * with SELECT schemavar. Users expects composite value (one attribute)
+ * from first query, but scalar from second query. This schisma can be
+ * solved by disallowing SELECT . FROM schemavar for scalar variables.
+ *
+ * On second hand - without additional fields, just with reloftype is
+ * not possible to store typmod. So all variables can be typmod less.
+ * Is not possible to store default expressions. So final design should
+ * be based on aux composite types for scalar variables.
+ *
+ * Theoretically, there can be used a reltype and reloftype together.
+ * reloftype will be scalar, and reltype will be composite one field
+ * row type. When reloftype = reltype, then schema variable is based
+ * on composite type, else schema variable is of scalar type.
+ */
+ stmt->ofTypename = var->typeName;
+
+ address = DefineRelation(stmt, RELKIND_VARIABLE, InvalidOid, NULL, NULL);
+ Assert(address.objectId != InvalidOid);
+
+ return address;
+}
+
+/*
+ * Implementation of schemavar cache. It is question if it should be in this place, or
+ * it should be storage related or cache related place? But for this moment (PoC) it
+ * can be here. Cache is implemented as hash table with own memory context.
+ */
+
+/*
+ * Create the hash table for storing schema variables
+ */
+static void
+create_schemavar_hashtable(void)
+{
+ HASHCTL ctl;
+
+ /* set callbacks */
+ if (first_time)
+ {
+
+ CacheRegisterSyscacheCallback(RELOID,
+ InvalidateSchemaVarCacheCallback,
+ (Datum) 0);
+ RegisterXactCallback(recheck_schema_variables, NULL);
+
+ first_time = false;
+ }
+
+ /* needs own long life memory context */
+ if (SchemaVarMemoryContext == NULL)
+ {
+ SchemaVarMemoryContext = AllocSetContextCreate(TopMemoryContext,
+ "schema variables",
+ ALLOCSET_START_SMALL_SIZES);
+ }
+
+ memset(&ctl, 0, sizeof(ctl));
+ ctl.keysize = sizeof(Oid);
+ ctl.entrysize = sizeof(SchemaVarData);
+ ctl.hcxt = SchemaVarMemoryContext;
+
+ schemavarhashtab = hash_create("Schema variables", 64, &ctl,
+ HASH_ELEM | HASH_BLOBS | HASH_CONTEXT);
+ cache_is_valid = true;
+}
+
+/*
+ * Fast drop complete content of schema variables
+ */
+void
+ResetSchemaVariablesCache(void)
+{
+ if (schemavarhashtab)
+ {
+ hash_destroy(schemavarhashtab);
+ schemavarhashtab = NULL;
+ }
+
+ if (SchemaVarMemoryContext != NULL)
+ {
+ MemoryContextReset(SchemaVarMemoryContext);
+ }
+}
+
+/*
+ * Copy datum value to schema variables cache place
+ */
+static void
+SetValue(SchemaVar var,
+ Datum value, bool isNull,
+ Oid typid, int32 typmod)
+{
+ /* release previously stored value */
+ if (var->freeval)
+ {
+ pfree(DatumGetPointer(var->value));
+ var->freeval = false;
+ }
+
+ if (!isNull)
+ {
+ MemoryContext oldcxt;
+
+ /*
+ * cast the value if conversion is necessary.
+ * Expecting: current context is short context.
+ *
+ * QUESTION: how much should be this cast tolerant/strict?
+ */
+ if (var->typid != typid || var->typmod != typmod)
+ {
+ value = datumCast(value,
+ var->typid, var->typmod,
+ typid, typmod);
+ }
+
+ var->isnull = false;
+
+ oldcxt = MemoryContextSwitchTo(SchemaVarMemoryContext);
+
+ var->value = datumCopy(value, var->typbyval, var->typlen);
+ if (var->value != value)
+ var->freeval = true;
+
+ MemoryContextSwitchTo(oldcxt);
+ }
+ else
+ {
+ var->value = (Datum) 0;
+ var->isnull = true;
+ }
+}
+
+/*
+ * Access functions to schema variables.
+ */
+void
+SetSchemaVariable(Oid varid, Datum value, bool isNull,
+ Oid typid, int32 typmod,
+ int16 typlen, bool typbyval)
+{
+ SchemaVar var;
+ bool found;
+
+ if (schemavarhashtab == NULL)
+ {
+ /* don't init hashtable for NULL values */
+ if (isNull)
+ return;
+
+ create_schemavar_hashtable();
+ }
+
+ var = (SchemaVar) hash_search(schemavarhashtab, &varid, HASH_ENTER, &found);
+ if (!found)
+ {
+ HeapTuple tp;
+ Form_pg_class vartup;
+
+ var->value = (Datum) 0;
+ var->isnull = true;
+ var->freeval = false;
+
+ /* now, type info for schema variable is collected */
+ tp = SearchSysCache1(RELOID, ObjectIdGetDatum(varid));
+ if (!HeapTupleIsValid(tp))
+ elog(ERROR, "cache lookup faild for variable %u", varid);
+
+ vartup = (Form_pg_class) GETSTRUCT(tp);
+ var->typid = vartup->reloftype;
+
+ /* typmod is not saved */
+ var->typmod = -1;
+
+ ReleaseSysCache(tp);
+
+ get_typlenbyval(var->typid, &var->typlen, &var->typbyval);
+ }
+
+ SetValue(var, value, isNull, typid, typmod);
+}
+
+/*
+ * Returns variable name
+ */
+char *
+get_schemavar_name(Oid varid)
+{
+ HeapTuple relTup;
+ Form_pg_class relForm;
+ char *nspname;
+ char *relname;
+
+ relTup = SearchSysCache1(RELOID,
+ ObjectIdGetDatum(varid));
+ if (!HeapTupleIsValid(relTup))
+ elog(ERROR, "cache lookup failed for schema variable %u", varid);
+ relForm = (Form_pg_class) GETSTRUCT(relTup);
+
+ /* Qualify the name if not visible in search path */
+ if (RelationIsVisible(varid))
+ nspname = NULL;
+ else
+ nspname = get_namespace_name(relForm->relnamespace);
+
+ relname = quote_qualified_identifier(nspname, NameStr(relForm->relname));
+
+ ReleaseSysCache(relTup);
+
+ return relname;
+}
+
+/*
+ * Securized versions SetSchemaVariable
+ */
+void
+SetSchemaVariableSecure(Oid varid, Datum value, bool isNull,
+ Oid typid, int32 typmod,
+ int16 typlen, bool typbyval)
+{
+ AclResult aclresult;
+
+ /* Check permissions */
+ aclresult = pg_class_aclcheck(varid, GetUserId(), ACL_UPDATE);
+ if (aclresult != ACLCHECK_OK)
+ aclcheck_error(aclresult, OBJECT_VARIABLE, get_schemavar_name(varid));
+
+ SetSchemaVariable(varid, value, isNull, typid, typmod, typlen, typbyval);
+}
+
+/*
+ * Cast datum
+ */
+static Datum
+datumCast(Datum value,
+ Oid target_typid, int target_typmod,
+ Oid source_typid, int source_typmod)
+{
+ CoercionPathType cpathtype;
+ Oid cfuncid;
+ Datum result = (Datum) 0;
+ bool is_binary_cast = false;
+
+ if (target_typid != source_typid)
+ {
+ cpathtype = find_coercion_pathway(target_typid, source_typid,
+ COERCION_EXPLICIT,
+ &cfuncid);
+
+ if (cpathtype == COERCION_PATH_NONE)
+ elog(ERROR, "could not find cast from %s to %s",
+ format_type_be(source_typid),
+ format_type_be(target_typid));
+
+ if (cpathtype == COERCION_PATH_RELABELTYPE)
+ {
+ result = value;
+ is_binary_cast = true;
+ }
+ else if (cpathtype == COERCION_PATH_COERCEVIAIO)
+ {
+ Oid outfunc;
+ Oid infunc;
+ Oid ioparam;
+ bool isVarlena;
+ char *str;
+
+ getTypeOutputInfo(source_typid, &outfunc, &isVarlena);
+ str = OidOutputFunctionCall(outfunc, value);
+
+ getTypeInputInfo(target_typid, &infunc, &ioparam);
+ result = OidInputFunctionCall(infunc, str, ioparam, -1);
+ }
+ else if (cpathtype == COERCION_PATH_FUNC)
+ {
+ result = OidFunctionCall3(cfuncid,
+ value,
+ Int32GetDatum(target_typmod),
+ BoolGetDatum(false));
+ }
+ }
+ else
+ {
+ result = value;
+ is_binary_cast = true;
+ }
+
+ if (target_typmod < 1 || (target_typmod == source_typmod && is_binary_cast))
+ return result;
+
+ cpathtype = find_typmod_coercion_function(target_typid, &cfuncid);
+ if (cpathtype == COERCION_PATH_FUNC)
+ {
+ result = OidFunctionCall3(cfuncid,
+ result,
+ Int32GetDatum(target_typmod),
+ BoolGetDatum(false));
+ }
+
+ return result;
+}
+
+Datum
+GetSchemaVariable(Oid varid, bool *isNull,
+ Oid typid, int32 typmod,
+ int16 typlen, bool typbyval)
+{
+ Assert(varid != InvalidOid);
+
+ if (schemavarhashtab != NULL)
+ {
+ SchemaVar var;
+ bool found;
+
+ var = (SchemaVar) hash_search(schemavarhashtab,
+ &varid, HASH_FIND, &found);
+
+ if (found && !var->isnull)
+ {
+ Datum result;
+
+ result = datumCast(var->value, typid, typmod,
+ var->typid, var->typmod);
+ *isNull = false;
+
+ if (result != var->value)
+ return result;
+ else
+ return datumCopy(result, typbyval, typlen);
+ }
+ }
+
+ /*
+ * This implementation is simple, because default expressions
+ * are not supported. With support of default expression, there
+ * should be insert schema variable into cache. Not supported yet,
+ * so do just simply work.
+ */
+ *isNull = true;
+ return (Datum) 0;
+}
+
+/*
+ * Securized version of GetSchemaVariable
+ */
+Datum
+GetSchemaVariableSecure(Oid varid, bool *isNull,
+ Oid typid, int32 typmod,
+ int16 typlen, bool typbyval)
+{
+ AclResult aclresult;
+
+ /* Check permissions */
+ aclresult = pg_class_aclcheck(varid, GetUserId(), ACL_SELECT);
+ if (aclresult != ACLCHECK_OK)
+ aclcheck_error(aclresult, OBJECT_VARIABLE, get_schemavar_name(varid));
+
+ return GetSchemaVariable(varid, isNull, typid, typmod, typlen, typbyval);
+}
+
+/*
+ * V1 function API
+ *
+ * void set_schema_variable(var regclass, value anyelement);
+ * anyelement get_schema_variable(var regclass, expected_type anyelement)
+ *
+ */
+Datum
+set_schema_variable(PG_FUNCTION_ARGS)
+{
+ Oid varid;
+ Datum value;
+ bool isNull;
+ Oid typid;
+ int16 typlen;
+ bool typbyval;
+
+ if (PG_ARGISNULL(0))
+ ereport(ERROR,
+ (errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
+ errmsg("null value not allowed for variable identity")));
+
+ varid = PG_GETARG_OID(0);
+
+ if (!PG_ARGISNULL(1))
+ {
+ value = PG_GETARG_DATUM(1);
+ isNull = false;
+ }
+ else
+ {
+ value = (Datum) 0;
+ isNull = true;
+ }
+
+ typid = get_fn_expr_argtype(fcinfo->flinfo, 1);
+ if (typid == InvalidOid)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("could not determine input data type")));
+
+ get_typlenbyval(typid, &typlen, &typbyval);
+ SetSchemaVariableSecure(varid, value, isNull, typid, -1, typlen, typbyval);
+
+ PG_RETURN_VOID();
+}
+
+Datum
+get_schema_variable(PG_FUNCTION_ARGS)
+{
+ Oid varid;
+ Oid typid;
+ int16 typlen;
+ bool typbyval;
+ bool isNull;
+ Datum result;
+
+ if (PG_ARGISNULL(0))
+ ereport(ERROR,
+ (errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
+ errmsg("null value not allowed for variable identity")));
+
+ varid = PG_GETARG_OID(0);
+
+ typid = get_fn_expr_argtype(fcinfo->flinfo, 1);
+ if (typid == InvalidOid)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("could not determine input data type")));
+
+ get_typlenbyval(typid, &typlen, &typbyval);
+ result = GetSchemaVariableSecure(varid, &isNull, typid, -1, typlen, typbyval);
+
+ if (isNull)
+ PG_RETURN_NULL();
+ else
+ PG_RETURN_DATUM(result);
+}
+
+/*
+ * Results:
+ *=
+
+1. The schema variables are similar to temporary tables - but the data are not saved
+ in 8KB blocks, so new storage and some simple storage manager should be created. We
+ would not to use local buffer due allocation 8KB as minimun, creating temp file.
+
+2. We should to work with typmod, so pg_attribute entry should be created anytime.
+
+3. A risk of collisions of variable and table name will be reduced, when variables
+ and tables cannot to have same name.
+
+4. If schema variables are pg_class based, then some current syntax has sense
+
+ We can support UPDATE and SELECT commands on variables.
+
+ possible syntaxes:
+
+ SELECT varname;
+ SELECT varname.field;
+ SELECT varname FROM varname; -- when var is scalar, then varname in target list is scalar
+
+ UPDATE varname SET varname = 10; -- scalar version
+ UPDATE varname SET varname = (x,y,z) -- composite version
+ UPDATE varname SET varname.field1 = x, varname.field2 = y, ..
+
+ LET varname = expr;
+ LET varname.field1 = expr;
+ LET varname = (x, y, z); -- composite version
+
+5. LET cmd can be implemented as CMD (like INSERT, UPDATE, DELETE) or Utility (like
+ CreateTableAsSelect). Prefer first option, because there can be prepared, can be
+ used together with EXPLAIN, etc.
+
+ Expected form:
+ LET foo = (SELECT id FROM boo WHERE some = 'hello');
+
+ so possibility to run EXPLAIN LET .. has good enough benefit
+*/
\ No newline at end of file
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 89454d8e80..13af916c11 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -10048,6 +10048,7 @@ ATExecChangeOwner(Oid relationOid, Oid newOwnerId, bool recursing, LOCKMODE lock
case RELKIND_MATVIEW:
case RELKIND_FOREIGN_TABLE:
case RELKIND_PARTITIONED_TABLE:
+ case RELKIND_VARIABLE:
/* ok to change owner */
break;
case RELKIND_INDEX:
diff --git a/src/backend/executor/execExpr.c b/src/backend/executor/execExpr.c
index c6eb3ebacf..53ea890517 100644
--- a/src/backend/executor/execExpr.c
+++ b/src/backend/executor/execExpr.c
@@ -33,6 +33,7 @@
#include "access/nbtree.h"
#include "catalog/objectaccess.h"
#include "catalog/pg_type.h"
+#include "commands/schemavar.h"
#include "executor/execExpr.h"
#include "executor/nodeSubplan.h"
#include "funcapi.h"
@@ -723,6 +724,7 @@ ExecInitExprRec(Expr *node, ExprState *state,
{
Param *param = (Param *) node;
ParamListInfo params;
+ AclResult aclresult;
switch (param->paramkind)
{
@@ -730,6 +732,23 @@ ExecInitExprRec(Expr *node, ExprState *state,
scratch.opcode = EEOP_PARAM_EXEC;
scratch.d.param.paramid = param->paramid;
scratch.d.param.paramtype = param->paramtype;
+ ExprEvalPushStep(state, &scratch);
+ break;
+ case PARAM_SCHEMA_VARIABLE:
+ /* Check permission to read schema variable */
+ aclresult = pg_class_aclcheck(param->paramid, GetUserId(), ACL_SELECT);
+ if (aclresult != ACLCHECK_OK)
+ aclcheck_error(aclresult, OBJECT_VARIABLE, get_schemavar_name(param->paramid));
+
+ scratch.opcode = EEOP_PARAM_SCHEMA_VARIABLE;
+ scratch.d.param.paramid = param->paramid;
+ scratch.d.param.paramtype = param->paramtype;
+ scratch.d.param.paramtypmod = param->paramtypmod;
+
+ get_typlenbyval(param->paramtype,
+ &scratch.d.param.paramtyplen,
+ &scratch.d.param.paramtypbyval);
+
ExprEvalPushStep(state, &scratch);
break;
case PARAM_EXTERN:
diff --git a/src/backend/executor/execExprInterp.c b/src/backend/executor/execExprInterp.c
index f646fd9c51..7a3b283039 100644
--- a/src/backend/executor/execExprInterp.c
+++ b/src/backend/executor/execExprInterp.c
@@ -59,6 +59,7 @@
#include "access/tuptoaster.h"
#include "catalog/pg_type.h"
#include "commands/sequence.h"
+#include "commands/schemavar.h"
#include "executor/execExpr.h"
#include "executor/nodeSubplan.h"
#include "funcapi.h"
@@ -350,6 +351,7 @@ ExecInterpExpr(ExprState *state, ExprContext *econtext, bool *isnull)
&&CASE_EEOP_PARAM_EXEC,
&&CASE_EEOP_PARAM_EXTERN,
&&CASE_EEOP_PARAM_CALLBACK,
+ &&CASE_EEOP_PARAM_SCHEMA_VARIABLE,
&&CASE_EEOP_CASE_TESTVAL,
&&CASE_EEOP_MAKE_READONLY,
&&CASE_EEOP_IOCOERCE,
@@ -1031,6 +1033,23 @@ ExecInterpExpr(ExprState *state, ExprContext *econtext, bool *isnull)
EEO_NEXT();
}
+ EEO_CASE(EEOP_PARAM_SCHEMA_VARIABLE)
+ {
+ Datum d;
+ bool isnull;
+
+ d = GetSchemaVariable(op->d.param.paramid, &isnull,
+ op->d.param.paramtype,
+ -1,
+ op->d.param.paramtyplen,
+ op->d.param.paramtypbyval);
+
+ *op->resvalue = d;
+ *op->resnull = isnull;
+
+ EEO_NEXT();
+ }
+
EEO_CASE(EEOP_PARAM_CALLBACK)
{
/* allow an extension module to supply a PARAM_EXTERN value */
diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c
index 410921cc40..04513b10e0 100644
--- a/src/backend/executor/execMain.c
+++ b/src/backend/executor/execMain.c
@@ -210,6 +210,7 @@ standard_ExecutorStart(QueryDesc *queryDesc, int eflags)
switch (queryDesc->operation)
{
case CMD_SELECT:
+ case CMD_LET:
/*
* SELECT FOR [KEY] UPDATE/SHARE and modifying CTEs need to mark
@@ -1119,6 +1120,14 @@ CheckValidResultRel(ResultRelInfo *resultRelInfo, CmdType operation)
errmsg("cannot change TOAST relation \"%s\"",
RelationGetRelationName(resultRel))));
break;
+ case RELKIND_VARIABLE:
+ /* only LET command can change a variable */
+ if (operation != CMD_LET)
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("cannot change schema variable \"%s\"",
+ RelationGetRelationName(resultRel))));
+ break;
case RELKIND_VIEW:
/*
@@ -1286,6 +1295,13 @@ CheckValidRowMarkRel(Relation rel, RowMarkType markType)
errmsg("cannot lock rows in foreign table \"%s\"",
RelationGetRelationName(rel))));
break;
+ case RELKIND_VARIABLE:
+ /* Must disallow this because we there are not rows */
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("cannot lock rows in schema variable \"%s\"",
+ RelationGetRelationName(rel))));
+ break;
default:
ereport(ERROR,
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c
index 2a8ecbd830..f8e478aa42 100644
--- a/src/backend/executor/nodeModifyTable.c
+++ b/src/backend/executor/nodeModifyTable.c
@@ -39,6 +39,7 @@
#include "access/htup_details.h"
#include "access/xact.h"
+#include "commands/schemavar.h"
#include "commands/trigger.h"
#include "executor/execPartition.h"
#include "executor/executor.h"
@@ -68,6 +69,7 @@ static void ExecSetupChildParentMapForSubplan(ModifyTableState *mtstate);
static TupleConversionMap *tupconv_map_for_subplan(ModifyTableState *node,
int whichplan);
+
/*
* Verify that the tuples to be produced by INSERT or UPDATE match the
* target relation's rowtype
@@ -1568,6 +1570,81 @@ ExecOnConflictUpdate(ModifyTableState *mtstate,
}
+
+
+
+/* ----------------------------------------------------------------
+ * ExecLet
+ *
+ * For LET, we have to update target variable,
+ * Returns NULL, there are not RETURNING clause.
+ * ----------------------------------------------------------------
+ */
+static TupleTableSlot *
+ExecLet(ModifyTableState *mtstate,
+ TupleTableSlot *slot,
+ EState *estate,
+ bool canSetTag)
+{
+ HeapTuple tuple;
+ ResultRelInfo *resultRelInfo;
+ Relation resultRelationDesc;
+ TupleDesc tupdesc;
+ bool isnull = true;
+ Datum value;
+ Form_pg_attribute attr = NULL;
+ Oid varid;
+
+ if (slot != NULL && !slot->tts_isempty)
+ {
+ tuple = slot->tts_tuple;
+ tupdesc = slot->tts_tupleDescriptor;
+
+ Assert(tupdesc != NULL);
+
+ /* should be checked before */
+ if (tupdesc->natts != 1)
+ elog(ERROR, "unexpected number of attributes");
+
+ attr = TupleDescAttr(tupdesc, 0);
+
+ if (!slot->tts_isnull[0])
+ {
+ isnull = false;
+ value = slot->tts_values[0];
+ }
+ }
+
+ /*
+ * Now, es_result_relation_info is empty, but can be initialized
+ * to structure of used schema variable.
+ */
+ resultRelInfo = estate->es_result_relation_info;
+ resultRelationDesc = resultRelInfo->ri_RelationDesc;
+ varid = resultRelationDesc->rd_id;
+
+ if (!isnull)
+ {
+ /* expecting so variable and expression are equal */
+ SetSchemaVariable(varid, value, isnull,
+ attr->atttypid, -1,
+ attr->attlen, attr->attbyval);
+ }
+ else
+ {
+ SetSchemaVariable(varid, (Datum) 0, true,
+ InvalidOid, -1, -1, false);
+ }
+
+ if (canSetTag)
+ {
+ Assert(estate->es_processed == 0);
+ (estate->es_processed)++;
+ }
+
+ return NULL;
+}
+
/*
* Process BEFORE EACH STATEMENT triggers
*/
@@ -1598,6 +1675,9 @@ fireBSTriggers(ModifyTableState *node)
case CMD_DELETE:
ExecBSDeleteTriggers(node->ps.state, resultRelInfo);
break;
+ case CMD_LET:
+ /* there are no trigger */
+ break;
default:
elog(ERROR, "unknown operation");
break;
@@ -1652,6 +1732,9 @@ fireASTriggers(ModifyTableState *node)
ExecASDeleteTriggers(node->ps.state, resultRelInfo,
node->mt_transition_capture);
break;
+ case CMD_LET:
+ /* variables has not triggers */
+ break;
default:
elog(ERROR, "unknown operation");
break;
@@ -2056,6 +2139,9 @@ ExecModifyTable(PlanState *pstate)
&node->mt_epqstate, estate,
NULL, true, node->canSetTag);
break;
+ case CMD_LET:
+ slot = ExecLet(node, slot, estate, node->canSetTag);
+ break;
default:
elog(ERROR, "unknown operation");
break;
@@ -2562,6 +2648,7 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags)
break;
case CMD_UPDATE:
case CMD_DELETE:
+ case CMD_LET:
junk_filter_needed = true;
break;
default:
diff --git a/src/backend/executor/spi.c b/src/backend/executor/spi.c
index 9fc4431b80..310bc3f2c7 100644
--- a/src/backend/executor/spi.c
+++ b/src/backend/executor/spi.c
@@ -2404,6 +2404,9 @@ _SPI_pquery(QueryDesc *queryDesc, bool fire_triggers, uint64 tcount)
else
res = SPI_OK_UPDATE;
break;
+ case CMD_LET:
+ res = SPI_OK_UTILITY;
+ break;
default:
return SPI_ERROR_OPUNKNOWN;
}
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index 82255b0d1d..a53fe4108c 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -3066,6 +3066,17 @@ _copySelectStmt(const SelectStmt *from)
return newnode;
}
+static LetStmt *
+_copyLetStmt(const LetStmt *from)
+{
+ LetStmt *newnode = makeNode(LetStmt);
+
+ COPY_NODE_FIELD(variable);
+ COPY_NODE_FIELD(selectStmt);
+
+ return newnode;
+}
+
static SetOperationStmt *
_copySetOperationStmt(const SetOperationStmt *from)
{
@@ -5101,6 +5112,9 @@ copyObjectImpl(const void *from)
case T_SelectStmt:
retval = _copySelectStmt(from);
break;
+ case T_LetStmt:
+ retval = _copyLetStmt(from);
+ break;
case T_SetOperationStmt:
retval = _copySetOperationStmt(from);
break;
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index b9bc8e38d7..e35b47fea9 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -1067,6 +1067,15 @@ _equalSelectStmt(const SelectStmt *a, const SelectStmt *b)
return true;
}
+static bool
+_equalLetStmt(const LetStmt *a, const LetStmt *b)
+{
+ COMPARE_NODE_FIELD(variable);
+ COMPARE_NODE_FIELD(selectStmt);
+
+ return true;
+}
+
static bool
_equalSetOperationStmt(const SetOperationStmt *a, const SetOperationStmt *b)
{
@@ -3233,6 +3242,9 @@ equal(const void *a, const void *b)
case T_SelectStmt:
retval = _equalSelectStmt(a, b);
break;
+ case T_LetStmt:
+ retval = _equalLetStmt(a, b);
+ break;
case T_SetOperationStmt:
retval = _equalSetOperationStmt(a, b);
break;
diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c
index 6c76c41ebe..8d24818c9f 100644
--- a/src/backend/nodes/nodeFuncs.c
+++ b/src/backend/nodes/nodeFuncs.c
@@ -3444,6 +3444,16 @@ raw_expression_tree_walker(Node *node,
return true;
}
break;
+ case T_LetStmt:
+ {
+ LetStmt *stmt = (LetStmt *) node;
+
+ if (walker(stmt->variable, context))
+ return true;
+ if (walker(stmt->selectStmt, context))
+ return true;
+ }
+ break;
case T_A_Expr:
{
A_Expr *expr = (A_Expr *) node;
diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c
index 89f27ce0eb..f4d8756487 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -1251,12 +1251,15 @@ max_parallel_hazard_walker(Node *node, max_parallel_hazard_context *context)
* PARAM_EXEC Params listed in safe_param_ids, meaning they could be
* either generated within the worker or can be computed in master and
* then their value can be passed to the worker.
+ * PARAM_SCHEMA_VARIABLE params are newer changed by workers, so they can be
+ * safe.
*/
else if (IsA(node, Param))
{
Param *param = (Param *) node;
- if (param->paramkind == PARAM_EXTERN)
+ if (param->paramkind == PARAM_EXTERN ||
+ param->paramkind == PARAM_SCHEMA_VARIABLE)
return false;
if (param->paramkind != PARAM_EXEC ||
diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c
index e7b2bc7e73..f22eab422e 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -42,6 +42,7 @@
#include "parser/parse_target.h"
#include "parser/parsetree.h"
#include "rewrite/rewriteManip.h"
+#include "utils/builtins.h"
#include "utils/rel.h"
@@ -54,6 +55,7 @@ static Query *transformInsertStmt(ParseState *pstate, InsertStmt *stmt);
static List *transformInsertRow(ParseState *pstate, List *exprlist,
List *stmtcols, List *icolumns, List *attrnos,
bool strip_indirection);
+static Query *transformLetStmt(ParseState *pstate, LetStmt *stmt);
static OnConflictExpr *transformOnConflictClause(ParseState *pstate,
OnConflictClause *onConflictClause);
static int count_rowexpr_columns(ParseState *pstate, Node *expr);
@@ -263,6 +265,7 @@ transformStmt(ParseState *pstate, Node *parseTree)
case T_InsertStmt:
case T_UpdateStmt:
case T_DeleteStmt:
+ case T_LetStmt:
(void) test_raw_expression_coverage(parseTree, NULL);
break;
default:
@@ -300,6 +303,10 @@ transformStmt(ParseState *pstate, Node *parseTree)
}
break;
+ case T_LetStmt:
+ result = transformLetStmt(pstate, (LetStmt *) parseTree);
+ break;
+
/*
* Special cases
*/
@@ -358,6 +365,7 @@ analyze_requires_snapshot(RawStmt *parseTree)
case T_DeleteStmt:
case T_UpdateStmt:
case T_SelectStmt:
+ case T_LetStmt:
result = true;
break;
@@ -1532,6 +1540,207 @@ transformValuesClause(ParseState *pstate, SelectStmt *stmt)
return qry;
}
+/*
+ * transformLetStmt -
+ * transform an Let Statement
+ */
+static Query *
+transformLetStmt(ParseState *pstate, LetStmt *stmt)
+{
+ Query *qry = makeNode(Query);
+ List *exprList = NIL;
+ List *exprListCoer = NIL;
+ List *sub_rtable;
+ List *sub_namespace;
+ RangeTblEntry *rte;
+ RangeTblRef *rtr;
+ ListCell *lc;
+ AclMode targetPerms;
+ ParseState *sub_pstate;
+ Query *selectQuery;
+ int i = 0;
+
+ Relation rd;
+ Oid vartypid = InvalidOid;
+
+ /* There can't be any outer WITH to worry about */
+ Assert(pstate->p_ctenamespace == NIL);
+
+ qry->commandType = CMD_LET;
+ pstate->p_is_let = true;
+
+ /*
+ * If a non-nil rangetable/namespace was passed in, and we are doing
+ * INSERT/SELECT, arrange to pass the rangetable/namespace down to the
+ * SELECT. This can only happen if we are inside a CREATE RULE, and in
+ * that case we want the rule's OLD and NEW rtable entries to appear as
+ * part of the SELECT's rtable, not as outer references for it. (Kluge!)
+ * The SELECT's joinlist is not affected however. We must do this before
+ * adding the target table to the INSERT's rtable.
+ */
+ sub_rtable = pstate->p_rtable;
+ pstate->p_rtable = NIL;
+ sub_namespace = pstate->p_namespace;
+ pstate->p_namespace = NIL;
+
+ targetPerms = ACL_UPDATE;
+ qry->resultRelation = setTargetTable(pstate, stmt->variable,
+ false, false, targetPerms);
+
+ rd = pstate->p_target_relation;
+ vartypid = rd->rd_rel->reloftype;
+
+ /*
+ * We make the sub-pstate a child of the outer pstate so that it can
+ * see any Param definitions supplied from above. Since the outer
+ * pstate's rtable and namespace are presently empty, there are no
+ * side-effects of exposing names the sub-SELECT shouldn't be able to
+ * see.
+ */
+ sub_pstate = make_parsestate(pstate);
+
+ /*
+ * Process the source SELECT.
+ *
+ * It is important that this be handled just like a standalone SELECT;
+ * otherwise the behavior of SELECT within INSERT might be different
+ * from a stand-alone SELECT. (Indeed, Postgres up through 6.5 had
+ * bugs of just that nature...)
+ *
+ * The sole exception is that we prevent resolving unknown-type
+ * outputs as TEXT. This does not change the semantics since if the
+ * column type matters semantically, it would have been resolved to
+ * something else anyway. Doing this lets us resolve such outputs as
+ * the target column's type, which we handle below.
+ */
+ sub_pstate->p_rtable = sub_rtable;
+ sub_pstate->p_joinexprs = NIL; /* sub_rtable has no joins */
+ sub_pstate->p_namespace = sub_namespace;
+ sub_pstate->p_resolve_unknowns = false;
+
+ selectQuery = transformStmt(sub_pstate, stmt->selectStmt);
+
+ free_parsestate(sub_pstate);
+
+ /* The grammar should have produced a SELECT */
+ if (!IsA(selectQuery, Query) ||
+ selectQuery->commandType != CMD_SELECT)
+ elog(ERROR, "unexpected non-SELECT command in LET ... SELECT");
+
+ /*
+ * Make the source be a subquery in the LET's rangetable, and add
+ * it to the LET's joinlist.
+ */
+ rte = addRangeTableEntryForSubquery(pstate,
+ selectQuery,
+ makeAlias("*SELECT*", NIL),
+ false,
+ false);
+ rtr = makeNode(RangeTblRef);
+ /* assume new rte is at end */
+ rtr->rtindex = list_length(pstate->p_rtable);
+ Assert(rte == rt_fetch(rtr->rtindex, pstate->p_rtable));
+ pstate->p_joinlist = lappend(pstate->p_joinlist, rtr);
+
+ /*----------
+ * Generate an expression list for the LET that selects all the
+ * non-resjunk columns from the subquery. (LET's tlist must be
+ * separate from the subquery's tlist because we may add datatype
+ * coercions, etc.)
+ *----------
+ */
+ exprList = NIL;
+ foreach(lc, selectQuery->targetList)
+ {
+ TargetEntry *tle = (TargetEntry *) lfirst(lc);
+ Expr *expr;
+
+ if (tle->resjunk)
+ continue;
+ if (tle->expr &&
+ (IsA(tle->expr, Const) ||IsA(tle->expr, Param)) &&
+ exprType((Node *) tle->expr) == UNKNOWNOID)
+ expr = tle->expr;
+ else
+ {
+ Var *var = makeVarFromTargetEntry(rtr->rtindex, tle);
+
+ var->location = exprLocation((Node *) tle->expr);
+ expr = (Expr *) var;
+ }
+ exprList = lappend(exprList, expr);
+ }
+
+ /*
+ * Because supports only scalar variables, we can only simple
+ * transformations and checks here.
+ */
+ if (list_length(exprList) != 1)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("expression is not scalar value"),
+ parser_errposition(pstate,
+ exprLocation((Node *) exprList))));
+
+ exprListCoer = NIL;
+ foreach(lc, exprList)
+ {
+ Node *orig_expr = (Node*) lfirst(lc);
+ Oid exprtypid = exprType((Node *) orig_expr);
+ Expr *expr;
+
+ expr = (Expr *)
+ coerce_to_target_type(pstate,
+ orig_expr, exprtypid,
+ vartypid, -1,
+ COERCION_ASSIGNMENT,
+ COERCE_IMPLICIT_CAST,
+ -1);
+
+ if (expr == NULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATATYPE_MISMATCH),
+ errmsg("variable \"%s\" is of type %s"
+ " but expression is of type %s",
+ RelationGetRelationName(rd),
+ format_type_be(vartypid),
+ format_type_be(exprtypid)),
+ errhint("You will need to rewrite or cast the expression."),
+ parser_errposition(pstate, exprLocation((Node *) orig_expr))));
+
+ exprListCoer = lappend(exprListCoer, expr);
+ }
+
+ /*
+ * Generate query's target list using the computed list of expressions.
+ * Also, mark all the target columns as needing insert permissions.
+ */
+ rte = pstate->p_target_rangetblentry;
+ qry->targetList = NIL;
+ foreach(lc, exprList)
+ {
+ Expr *expr = (Expr *) lfirst(lc);
+ TargetEntry *tle;
+
+ tle = makeTargetEntry(expr,
+ i + 1,
+ FigureColname((Node *)expr),
+ false);
+ qry->targetList = lappend(qry->targetList, tle);
+ }
+
+ /* done building the range table and jointree */
+ qry->rtable = pstate->p_rtable;
+ qry->jointree = makeFromExpr(pstate->p_joinlist, NULL);
+
+ qry->hasTargetSRFs = pstate->p_hasTargetSRFs;
+ qry->hasSubLinks = pstate->p_hasSubLinks;
+
+ assign_query_collations(pstate, qry);
+
+ return qry;
+}
+
/*
* transformSetOperationStmt -
* transforms a set-operations tree
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index d99f2be2c9..e79e341978 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -257,8 +257,8 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
ConstraintsSetStmt CopyStmt CreateAsStmt CreateCastStmt
CreateDomainStmt CreateExtensionStmt CreateGroupStmt CreateOpClassStmt
CreateOpFamilyStmt AlterOpFamilyStmt CreatePLangStmt
- CreateSchemaStmt CreateSeqStmt CreateStmt CreateStatsStmt CreateTableSpaceStmt
- CreateFdwStmt CreateForeignServerStmt CreateForeignTableStmt
+ CreateSchemaStmt CreateSchemaVarStmt CreateSeqStmt CreateStmt CreateStatsStmt
+ CreateTableSpaceStmt CreateFdwStmt CreateForeignServerStmt CreateForeignTableStmt
CreateAssertStmt CreateTransformStmt CreateTrigStmt CreateEventTrigStmt
CreateUserStmt CreateUserMappingStmt CreateRoleStmt CreatePolicyStmt
CreatedbStmt DeclareCursorStmt DefineStmt DeleteStmt DiscardStmt DoStmt
@@ -268,7 +268,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
DropTransformStmt
DropUserMappingStmt ExplainStmt FetchStmt
GrantStmt GrantRoleStmt ImportForeignSchemaStmt IndexStmt InsertStmt
- ListenStmt LoadStmt LockStmt NotifyStmt ExplainableStmt PreparableStmt
+ LetStmt ListenStmt LoadStmt LockStmt NotifyStmt ExplainableStmt PreparableStmt
CreateFunctionStmt AlterFunctionStmt ReindexStmt RemoveAggrStmt
RemoveFuncStmt RemoveOperStmt RenameStmt RevokeStmt RevokeRoleStmt
RuleActionStmt RuleActionStmtOrEmpty RuleStmt
@@ -647,7 +647,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
KEY
LABEL LANGUAGE LARGE_P LAST_P LATERAL_P
- LEADING LEAKPROOF LEAST LEFT LEVEL LIKE LIMIT LISTEN LOAD LOCAL
+ LEADING LEAKPROOF LEAST LEFT LET LEVEL LIKE LIMIT LISTEN LOAD LOCAL
LOCALTIME LOCALTIMESTAMP LOCATION LOCK_P LOCKED LOGGED
MAPPING MATCH MATERIALIZED MAXVALUE METHOD MINUTE_P MINVALUE MODE MONTH_P MOVE
@@ -685,8 +685,8 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
UNBOUNDED UNCOMMITTED UNENCRYPTED UNION UNIQUE UNKNOWN UNLISTEN UNLOGGED
UNTIL UPDATE USER USING
- VACUUM VALID VALIDATE VALIDATOR VALUE_P VALUES VARCHAR VARIADIC VARYING
- VERBOSE VERSION_P VIEW VIEWS VOLATILE
+ VACUUM VALID VALIDATE VALIDATOR VALUE_P VALUES VARCHAR VARIABLE VARIABLES
+ VARIADIC VARYING VERBOSE VERSION_P VIEW VIEWS VOLATILE
WHEN WHERE WHITESPACE_P WINDOW WITH WITHIN WITHOUT WORK WRAPPER WRITE
@@ -877,6 +877,7 @@ stmt :
| CreatePLangStmt
| CreateSchemaStmt
| CreateSeqStmt
+ | CreateSchemaVarStmt
| CreateStmt
| CreateSubscriptionStmt
| CreateStatsStmt
@@ -918,6 +919,7 @@ stmt :
| ListenStmt
| RefreshMatViewStmt
| LoadStmt
+ | LetStmt
| LockStmt
| NotifyStmt
| PrepareStmt
@@ -1378,6 +1380,7 @@ schema_stmt:
CreateStmt
| IndexStmt
| CreateSeqStmt
+ | CreateSchemaVarStmt
| CreateTrigStmt
| GrantStmt
| ViewStmt
@@ -1806,7 +1809,12 @@ DiscardStmt:
n->target = DISCARD_SEQUENCES;
$$ = (Node *) n;
}
-
+ | DISCARD VARIABLES
+ {
+ DiscardStmt *n = makeNode(DiscardStmt);
+ n->target = DISCARD_VARIABLES;
+ $$ = (Node *) n;
+ }
;
@@ -2002,6 +2010,24 @@ AlterTableStmt:
n->nowait = $14;
$$ = (Node *)n;
}
+ | ALTER VARIABLE qualified_name alter_table_cmds
+ {
+ AlterTableStmt *n = makeNode(AlterTableStmt);
+ n->relation = $3;
+ n->cmds = $4;
+ n->relkind = OBJECT_VARIABLE;
+ n->missing_ok = false;
+ $$ = (Node *)n;
+ }
+ | ALTER VARIABLE IF_P EXISTS qualified_name alter_table_cmds
+ {
+ AlterTableStmt *n = makeNode(AlterTableStmt);
+ n->relation = $5;
+ n->cmds = $6;
+ n->relkind = OBJECT_VARIABLE;
+ n->missing_ok = true;
+ $$ = (Node *)n;
+ }
;
alter_table_cmds:
@@ -4271,6 +4297,34 @@ NumericOnly_list: NumericOnly { $$ = list_make1($1); }
| NumericOnly_list ',' NumericOnly { $$ = lappend($1, $3); }
;
+/*****************************************************************************
+ *
+ * QUERY :
+ * CREATE VARIABLE seqname [AS] type
+ *
+ *****************************************************************************/
+
+CreateSchemaVarStmt:
+ CREATE OptTemp VARIABLE qualified_name opt_as Typename
+ {
+ CreateSchemaVarStmt *n = makeNode(CreateSchemaVarStmt);
+ $4->relpersistence = $2;
+ n->variable = $4;
+ n->typeName = $6;
+ n->if_not_exists = false;
+ $$ = (Node *)n;
+ }
+ | CREATE OptTemp VARIABLE IF_P NOT EXISTS qualified_name opt_as Typename
+ {
+ CreateSchemaVarStmt *n = makeNode(CreateSchemaVarStmt);
+ $7->relpersistence = $2;
+ n->variable = $7;
+ n->typeName = $9;
+ n->if_not_exists = true;
+ $$ = (Node *)n;
+ }
+ ;
+
/*****************************************************************************
*
* QUERIES :
@@ -6319,6 +6373,7 @@ drop_type_any_name:
| TEXT_P SEARCH DICTIONARY { $$ = OBJECT_TSDICTIONARY; }
| TEXT_P SEARCH TEMPLATE { $$ = OBJECT_TSTEMPLATE; }
| TEXT_P SEARCH CONFIGURATION { $$ = OBJECT_TSCONFIGURATION; }
+ | VARIABLE { $$ = OBJECT_VARIABLE; }
;
/* object types taking name_list */
@@ -6588,6 +6643,7 @@ comment_type_any_name:
| TEXT_P SEARCH DICTIONARY { $$ = OBJECT_TSDICTIONARY; }
| TEXT_P SEARCH PARSER { $$ = OBJECT_TSPARSER; }
| TEXT_P SEARCH TEMPLATE { $$ = OBJECT_TSTEMPLATE; }
+ | VARIABLE { $$ = OBJECT_VARIABLE; }
;
/* object types taking name */
@@ -6726,6 +6782,7 @@ security_label_type_any_name:
| TABLE { $$ = OBJECT_TABLE; }
| VIEW { $$ = OBJECT_VIEW; }
| MATERIALIZED VIEW { $$ = OBJECT_MATVIEW; }
+ | VARIABLE { $$ = OBJECT_VARIABLE; }
;
/* object types taking name */
@@ -7051,6 +7108,14 @@ privilege_target:
n->objs = $2;
$$ = n;
}
+ | VARIABLE qualified_name_list
+ {
+ PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
+ n->targtype = ACL_TARGET_OBJECT;
+ n->objtype = OBJECT_VARIABLE;
+ n->objs = $2;
+ $$ = n;
+ }
| FOREIGN DATA_P WRAPPER name_list
{
PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
@@ -7163,6 +7228,14 @@ privilege_target:
n->objs = $5;
$$ = n;
}
+ | ALL VARIABLES IN_P SCHEMA name_list
+ {
+ PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
+ n->targtype = ACL_TARGET_ALL_IN_SCHEMA;
+ n->objtype = OBJECT_VARIABLE;
+ n->objs = $5;
+ $$ = n;
+ }
| ALL FUNCTIONS IN_P SCHEMA name_list
{
PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
@@ -7345,6 +7418,7 @@ defacl_privilege_target:
| FUNCTIONS { $$ = OBJECT_FUNCTION; }
| ROUTINES { $$ = OBJECT_FUNCTION; }
| SEQUENCES { $$ = OBJECT_SEQUENCE; }
+ | VARIABLES { $$ = OBJECT_VARIABLE; }
| TYPES_P { $$ = OBJECT_TYPE; }
| SCHEMAS { $$ = OBJECT_SCHEMA; }
;
@@ -8933,6 +9007,26 @@ RenameStmt: ALTER AGGREGATE aggregate_with_argtypes RENAME TO name
n->missing_ok = false;
$$ = (Node *)n;
}
+ | ALTER VARIABLE qualified_name RENAME TO name
+ {
+ RenameStmt *n = makeNode(RenameStmt);
+ n->renameType = OBJECT_VARIABLE;
+ n->relation = $3;
+ n->subname = NULL;
+ n->newname = $6;
+ n->missing_ok = false;
+ $$ = (Node *)n;
+ }
+ | ALTER VARIABLE IF_P EXISTS qualified_name RENAME TO name
+ {
+ RenameStmt *n = makeNode(RenameStmt);
+ n->renameType = OBJECT_VARIABLE;
+ n->relation = $5;
+ n->subname = NULL;
+ n->newname = $8;
+ n->missing_ok = true;
+ $$ = (Node *)n;
+ }
;
opt_column: COLUMN { $$ = COLUMN; }
@@ -10649,7 +10743,8 @@ ExplainableStmt:
| CreateAsStmt
| CreateMatViewStmt
| RefreshMatViewStmt
- | ExecuteStmt /* by default all are $$=$1 */
+ | ExecuteStmt
+ | LetStmt /* by default all are $$=$1 */
;
explain_option_list:
@@ -10706,7 +10801,8 @@ PreparableStmt:
SelectStmt
| InsertStmt
| UpdateStmt
- | DeleteStmt /* by default all are $$=$1 */
+ | DeleteStmt
+ | LetStmt /* by default all are $$=$1 */
;
/*****************************************************************************
@@ -11105,6 +11201,30 @@ opt_hold: /* EMPTY */ { $$ = 0; }
| WITHOUT HOLD { $$ = 0; }
;
+/*****************************************************************************
+ *
+ * QUERY:
+ * LET STATEMENTS
+ *
+ *****************************************************************************/
+LetStmt: LET qualified_name '=' a_expr
+ {
+ LetStmt *n = makeNode(LetStmt);
+ SelectStmt *select = makeNode(SelectStmt);
+ ResTarget *res = makeNode(ResTarget);
+
+ res->name = NULL;
+ res->indirection = NIL;
+ res->val = (Node *) $4;
+ res->location = @4;
+ select->targetList = list_make1(res);
+ n->variable = $2;
+ n->selectStmt = (Node *) select;
+
+ $$ = (Node *) n;
+ }
+ ;
+
/*****************************************************************************
*
* QUERY:
@@ -15063,6 +15183,7 @@ unreserved_keyword:
| LARGE_P
| LAST_P
| LEAKPROOF
+ | LET
| LEVEL
| LISTEN
| LOAD
@@ -15211,6 +15332,8 @@ unreserved_keyword:
| VALIDATE
| VALIDATOR
| VALUE_P
+ | VARIABLE
+ | VARIABLES
| VARYING
| VERSION_P
| VIEW
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index d45926f27f..df03c6e688 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -509,6 +509,7 @@ static Node *
transformColumnRef(ParseState *pstate, ColumnRef *cref)
{
Node *node = NULL;
+ Node *variable = NULL;
char *nspname = NULL;
char *relname = NULL;
char *colname = NULL;
@@ -749,6 +750,70 @@ transformColumnRef(ParseState *pstate, ColumnRef *cref)
break;
}
+ /*
+ * Try to identify column ref as variable. Possible variants are
+ *
+ * A .. variable name
+ * A.B .. qualified variable name
+ */
+ switch (list_length(cref->fields))
+ {
+ case 1:
+ {
+ Node *field1 = (Node *) linitial(cref->fields);
+
+ if (IsA(field1, String))
+ {
+ char *varname = strVal(field1);
+
+ /* Try to identify as an unqualified column */
+ variable = toSchemaVariable(pstate,
+ NULL, varname,
+ cref->location);
+ }
+ break;
+ }
+ case 2:
+ {
+ Node *field1 = (Node *) linitial(cref->fields);
+ Node *field2 = (Node *) lsecond(cref->fields);
+
+ if (IsA(field1, String) && IsA(field2, String))
+ {
+ char *nspname = strVal(field1);
+ char *varname = strVal(field2);
+
+ /* Try to identify as an unqualified column */
+ variable = toSchemaVariable(pstate,
+ nspname, varname,
+ cref->location);
+ }
+ break;
+ }
+ default:
+
+ /*
+ * There can be another variants, more when composite variables
+ * will be supported. Currently only scalars are supported, so
+ * there are not necessary to solve other questions.
+ *
+ * do nothing
+ */
+ break;
+ }
+
+ if (variable != NULL)
+ {
+ if (node != NULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_AMBIGUOUS_COLUMN),
+ errmsg("column reference \"%s\" is ambiguous",
+ NameListToString(cref->fields)),
+ parser_errposition(pstate, cref->location)));
+
+ node = variable;
+ }
+
/*
* Now give the PostParseColumnRefHook, if any, a chance. We pass the
* translation-so-far so that it can throw an error if it wishes in the
diff --git a/src/backend/parser/parse_relation.c b/src/backend/parser/parse_relation.c
index 2625da5327..f7d9a0c939 100644
--- a/src/backend/parser/parse_relation.c
+++ b/src/backend/parser/parse_relation.c
@@ -1150,6 +1150,7 @@ parserOpenTable(ParseState *pstate, const RangeVar *relation, int lockmode)
setup_parser_errposition_callback(&pcbstate, pstate, relation->location);
rel = heap_openrv_extended(relation, lockmode, true);
+
if (rel == NULL)
{
if (relation->schemaname)
@@ -1180,6 +1181,24 @@ parserOpenTable(ParseState *pstate, const RangeVar *relation, int lockmode)
relation->relname)));
}
}
+
+ /*
+ * RELKIND_VARIABLE can be used only in LET command.
+ * Probably this check can be done elsewhere, but here I
+ * have a used relation and parse state together first time.
+ */
+ if (rel->rd_rel->relkind == RELKIND_VARIABLE && !pstate->p_is_let)
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("\"%s\" is an schema variable",
+ RelationGetRelationName(rel))));
+
+ if (pstate->p_is_let && rel->rd_rel->relkind != RELKIND_VARIABLE)
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("\"%s\" is not an schema variable",
+ RelationGetRelationName(rel))));
+
cancel_parser_errposition_callback(&pcbstate);
return rel;
}
@@ -3360,3 +3379,42 @@ isQueryUsingTempRelation_walker(Node *node, void *context)
isQueryUsingTempRelation_walker,
context);
}
+
+/*
+ * Try to replace ColumnRef by Param related to variable
+ */
+Node *
+toSchemaVariable(ParseState *pstate, char *nspname, char *varname, int location)
+{
+ Oid varid;
+ Param *param = NULL;
+
+ varid = RangeVarGetRelid(makeRangeVar(nspname, varname, -1), NoLock, true);
+ if (OidIsValid(varid))
+ {
+ HeapTuple tp;
+ Form_pg_class vartup;
+
+ /* now, type info for schema variable is collected */
+ tp = SearchSysCache1(RELOID, ObjectIdGetDatum(varid));
+ if (HeapTupleIsValid(tp))
+ {
+ vartup = (Form_pg_class) GETSTRUCT(tp);
+
+ if (vartup->relkind == RELKIND_VARIABLE)
+ {
+ param = makeNode(Param);
+ param->paramkind = PARAM_SCHEMA_VARIABLE;
+ param->paramid = varid;
+ param->paramtype = vartup->reloftype;
+ param->paramtypmod = -1;
+ param->paramcollid = get_typcollation(param->paramtype);
+ param->location = location;
+ }
+
+ ReleaseSysCache(tp);
+ }
+ }
+
+ return (Node *) param;
+}
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index d415d7180f..8c352f9293 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -106,6 +106,7 @@ typedef struct
List *views; /* CREATE VIEW items */
List *indexes; /* CREATE INDEX items */
List *triggers; /* CREATE TRIGGER items */
+ List *variables; /* CREATE VARIABLE items */
List *grants; /* GRANT items */
} CreateSchemaStmtContext;
@@ -3186,6 +3187,7 @@ transformCreateSchemaStmt(CreateSchemaStmt *stmt)
cxt.views = NIL;
cxt.indexes = NIL;
cxt.triggers = NIL;
+ cxt.variables = NIL;
cxt.grants = NIL;
/*
@@ -3251,6 +3253,14 @@ transformCreateSchemaStmt(CreateSchemaStmt *stmt)
}
break;
+ case T_CreateSchemaVarStmt:
+ {
+ CreateSchemaVarStmt *elp = (CreateSchemaVarStmt *) element;
+
+ setSchemaName(cxt.schemaname, &elp->variable->schemaname);
+ cxt.variables = lappend(cxt.variables, element);
+ }
+
case T_GrantStmt:
cxt.grants = lappend(cxt.grants, element);
break;
diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rewriteHandler.c
index 66253fc3d3..47a9b211d8 100644
--- a/src/backend/rewrite/rewriteHandler.c
+++ b/src/backend/rewrite/rewriteHandler.c
@@ -3337,7 +3337,7 @@ RewriteQuery(Query *parsetree, List *rewrite_events)
rt_entry_relation,
parsetree->resultRelation, NULL);
}
- else if (event == CMD_DELETE)
+ else if (event == CMD_DELETE || event == CMD_LET)
{
/* Nothing to do here */
}
diff --git a/src/backend/tcop/pquery.c b/src/backend/tcop/pquery.c
index 66cc5c35c6..34ddb79a3d 100644
--- a/src/backend/tcop/pquery.c
+++ b/src/backend/tcop/pquery.c
@@ -193,6 +193,10 @@ ProcessQuery(PlannedStmt *plan,
"DELETE " UINT64_FORMAT,
queryDesc->estate->es_processed);
break;
+ case CMD_LET:
+ snprintf(completionTag, COMPLETION_TAG_BUFSIZE,
+ "LET ");
+ break;
default:
strcpy(completionTag, "???");
break;
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index 3abe7d6155..72f0db5017 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -47,6 +47,7 @@
#include "commands/proclang.h"
#include "commands/publicationcmds.h"
#include "commands/schemacmds.h"
+#include "commands/schemavar.h"
#include "commands/seclabel.h"
#include "commands/sequence.h"
#include "commands/subscriptioncmds.h"
@@ -112,6 +113,7 @@ CommandIsReadOnly(PlannedStmt *pstmt)
case CMD_DELETE:
return false;
case CMD_UTILITY:
+ case CMD_LET:
/* For now, treat all utility commands as read/write */
return false;
default:
@@ -177,6 +179,7 @@ check_xact_readonly(Node *parsetree)
case T_CreateSchemaStmt:
case T_CreateSeqStmt:
case T_CreateStmt:
+ case T_CreateSchemaVarStmt:
case T_CreateTableAsStmt:
case T_RefreshMatViewStmt:
case T_CreateTableSpaceStmt:
@@ -1474,6 +1477,10 @@ ProcessUtilitySlow(ParseState *pstate,
address = AlterSequence(pstate, (AlterSeqStmt *) parsetree);
break;
+ case T_CreateSchemaVarStmt:
+ address = DefineSchemaVariable(pstate, (CreateSchemaVarStmt *) parsetree);
+ break;
+
case T_CreateTableAsStmt:
address = ExecCreateTableAs((CreateTableAsStmt *) parsetree,
queryString, params, queryEnv,
@@ -2046,6 +2053,9 @@ AlterObjectTypeCommandTag(ObjectType objtype)
case OBJECT_STATISTIC_EXT:
tag = "ALTER STATISTICS";
break;
+ case OBJECT_VARIABLE:
+ tag = "ALTER VARIABLE";
+ break;
default:
tag = "???";
break;
@@ -2095,6 +2105,10 @@ CreateCommandTag(Node *parsetree)
tag = "SELECT";
break;
+ case T_LetStmt:
+ tag = "LET";
+ break;
+
/* utility statements --- same whether raw or cooked */
case T_TransactionStmt:
{
@@ -2259,6 +2273,9 @@ CreateCommandTag(Node *parsetree)
case OBJECT_INDEX:
tag = "DROP INDEX";
break;
+ case OBJECT_VARIABLE:
+ tag = "DROP VARIABLE";
+ break;
case OBJECT_TYPE:
tag = "DROP TYPE";
break;
@@ -2513,6 +2530,10 @@ CreateCommandTag(Node *parsetree)
tag = "ALTER SEQUENCE";
break;
+ case T_CreateSchemaVarStmt:
+ tag = "CREATE VARIABLE";
+ break;
+
case T_DoStmt:
tag = "DO";
break;
@@ -2630,6 +2651,9 @@ CreateCommandTag(Node *parsetree)
case DISCARD_SEQUENCES:
tag = "DISCARD SEQUENCES";
break;
+ case DISCARD_VARIABLES:
+ tag = "DISCARD VARIABLES";
+ break;
default:
tag = "???";
}
@@ -2834,6 +2858,9 @@ CreateCommandTag(Node *parsetree)
case CMD_DELETE:
tag = "DELETE";
break;
+ case CMD_LET:
+ tag = "LET";
+ break;
case CMD_UTILITY:
tag = CreateCommandTag(stmt->utilityStmt);
break;
@@ -2952,6 +2979,10 @@ GetCommandLogLevel(Node *parsetree)
lev = LOGSTMT_ALL;
break;
+ case T_LetStmt:
+ lev = LOGSTMT_ALL;
+ break;
+
/* utility statements --- same whether raw or cooked */
case T_TransactionStmt:
lev = LOGSTMT_ALL;
@@ -3405,6 +3436,7 @@ GetCommandLogLevel(Node *parsetree)
switch (stmt->commandType)
{
case CMD_SELECT:
+ case CMD_LET:
lev = LOGSTMT_ALL;
break;
diff --git a/src/backend/utils/adt/acl.c b/src/backend/utils/adt/acl.c
index 0cfc297b65..fcd695836a 100644
--- a/src/backend/utils/adt/acl.c
+++ b/src/backend/utils/adt/acl.c
@@ -808,6 +808,10 @@ acldefault(ObjectType objtype, Oid ownerId)
world_default = ACL_USAGE;
owner_default = ACL_ALL_RIGHTS_TYPE;
break;
+ case OBJECT_VARIABLE:
+ world_default = ACL_NO_RIGHTS;
+ owner_default = ACL_ALL_RIGHTS_VARIABLE;
+ break;
default:
elog(ERROR, "unrecognized objtype: %d", (int) objtype);
world_default = ACL_NO_RIGHTS; /* keep compiler quiet */
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 28767a129a..fc6498c50d 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -41,6 +41,7 @@
#include "catalog/pg_type.h"
#include "commands/defrem.h"
#include "commands/tablespace.h"
+#include "commands/schemavar.h"
#include "common/keywords.h"
#include "executor/spi.h"
#include "funcapi.h"
@@ -379,6 +380,7 @@ static void get_update_query_targetlist_def(Query *query, List *targetList,
deparse_context *context,
RangeTblEntry *rte);
static void get_delete_query_def(Query *query, deparse_context *context);
+static void get_let_query_def(Query *query, deparse_context *context);
static void get_utility_query_def(Query *query, deparse_context *context);
static void get_basic_select_query(Query *query, deparse_context *context,
TupleDesc resultDesc);
@@ -4926,6 +4928,10 @@ get_query_def(Query *query, StringInfo buf, List *parentnamespace,
get_delete_query_def(query, &context);
break;
+ case CMD_LET:
+ get_let_query_def(query, &context);
+ break;
+
case CMD_NOTHING:
appendStringInfoString(buf, "NOTHING");
break;
@@ -6142,6 +6148,58 @@ get_insert_query_def(Query *query, deparse_context *context)
}
}
+/* ----------
+ * get_let_query_def - Parse back an LET parsetree
+ * ----------
+ */
+static void
+get_let_query_def(Query *query, deparse_context *context)
+{
+ StringInfo buf = context->buf;
+ RangeTblEntry *select_rte = NULL;
+ RangeTblEntry *rte;
+ ListCell *l;
+
+ /*
+ * If it's an INSERT ... SELECT or multi-row VALUES, there will be a
+ * single RTE for the SELECT or VALUES. Plain VALUES has neither.
+ */
+ foreach(l, query->rtable)
+ {
+ rte = (RangeTblEntry *) lfirst(l);
+
+ if (rte->rtekind == RTE_SUBQUERY)
+ {
+ if (select_rte)
+ elog(ERROR, "too many subquery RTEs in INSERT");
+ select_rte = rte;
+ }
+ }
+
+ /*
+ * Start the query with INSERT INTO relname
+ */
+ rte = rt_fetch(query->resultRelation, query->rtable);
+ Assert(rte->rtekind == RTE_RELATION);
+
+ if (PRETTY_INDENT(context))
+ {
+ context->indentLevel += PRETTYINDENT_STD;
+ appendStringInfoChar(buf, ' ');
+ }
+ appendStringInfo(buf, "LET %s ",
+ generate_relation_name(rte->relid, NIL));
+
+ appendStringInfo(buf, " = ");
+
+ if (select_rte)
+ {
+ /* Add the SELECT */
+ get_query_def(select_rte->subquery, buf, NIL, NULL,
+ context->prettyFlags, context->wrapColumn,
+ context->indentLevel);
+ }
+}
/* ----------
* get_update_query_def - Parse back an UPDATE parsetree
@@ -7216,6 +7274,13 @@ get_parameter(Param *param, deparse_context *context)
deparse_namespace *dpns;
ListCell *ancestor_cell;
+ if (param->paramkind == PARAM_SCHEMA_VARIABLE)
+ {
+ appendStringInfo(context->buf, "%s", get_schemavar_name(param->paramid));
+
+ return;
+ }
+
/*
* If it's a PARAM_EXEC parameter, try to locate the expression from which
* the parameter was computed. Note that failing to find a referent isn't
diff --git a/src/bin/pg_dump/common.c b/src/bin/pg_dump/common.c
index 2ec3627a68..b7d7b632b4 100644
--- a/src/bin/pg_dump/common.c
+++ b/src/bin/pg_dump/common.c
@@ -326,7 +326,8 @@ flagInhTables(Archive *fout, TableInfo *tblinfo, int numTables,
/* Some kinds never have parents */
if (tblinfo[i].relkind == RELKIND_SEQUENCE ||
tblinfo[i].relkind == RELKIND_VIEW ||
- tblinfo[i].relkind == RELKIND_MATVIEW)
+ tblinfo[i].relkind == RELKIND_MATVIEW ||
+ tblinfo[i].relkind == RELKIND_VARIABLE)
continue;
/*
@@ -475,7 +476,8 @@ flagInhAttrs(DumpOptions *dopt, TableInfo *tblinfo, int numTables)
/* Some kinds never have parents */
if (tbinfo->relkind == RELKIND_SEQUENCE ||
tbinfo->relkind == RELKIND_VIEW ||
- tbinfo->relkind == RELKIND_MATVIEW)
+ tbinfo->relkind == RELKIND_MATVIEW ||
+ tbinfo->relkind == RELKIND_VARIABLE)
continue;
/* Don't bother computing anything for non-target tables, either */
diff --git a/src/bin/pg_dump/pg_backup_archiver.c b/src/bin/pg_dump/pg_backup_archiver.c
index 7c5e8c018b..e9b8639ccf 100644
--- a/src/bin/pg_dump/pg_backup_archiver.c
+++ b/src/bin/pg_dump/pg_backup_archiver.c
@@ -3450,7 +3450,8 @@ _getObjectDescription(PQExpBuffer buf, TocEntry *te, ArchiveHandle *AH)
strcmp(type, "SERVER") == 0 ||
strcmp(type, "PUBLICATION") == 0 ||
strcmp(type, "SUBSCRIPTION") == 0 ||
- strcmp(type, "USER MAPPING") == 0)
+ strcmp(type, "USER MAPPING") == 0 ||
+ strcmp(type, "VARIABLE") == 0)
{
/* We already know that search_path was set properly */
appendPQExpBuffer(buf, "%s %s", type, fmtId(te->tag));
@@ -3629,6 +3630,7 @@ _printTocEntry(ArchiveHandle *AH, TocEntry *te, bool isData)
strcmp(te->desc, "TABLE") == 0 ||
strcmp(te->desc, "TYPE") == 0 ||
strcmp(te->desc, "VIEW") == 0 ||
+ strcmp(te->desc, "VARIABLE") == 0 ||
strcmp(te->desc, "MATERIALIZED VIEW") == 0 ||
strcmp(te->desc, "SEQUENCE") == 0 ||
strcmp(te->desc, "FOREIGN TABLE") == 0 ||
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 8ca83c06d6..e878db63e5 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -1303,10 +1303,11 @@ expand_table_name_patterns(Archive *fout,
"SELECT c.oid"
"\nFROM pg_catalog.pg_class c"
"\n LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace"
- "\nWHERE c.relkind in ('%c', '%c', '%c', '%c', '%c', '%c')\n",
+ "\nWHERE c.relkind in ('%c', '%c', '%c', '%c', '%c', '%c', '%c')\n",
RELKIND_RELATION, RELKIND_SEQUENCE, RELKIND_VIEW,
RELKIND_MATVIEW, RELKIND_FOREIGN_TABLE,
- RELKIND_PARTITIONED_TABLE);
+ RELKIND_PARTITIONED_TABLE,
+ RELKIND_VARIABLE);
processSQLNamePattern(GetConnection(fout), query, cell->val, true,
false, "n.nspname", "c.relname", NULL,
"pg_catalog.pg_table_is_visible(c.oid)");
@@ -2274,6 +2275,10 @@ makeTableDataInfo(DumpOptions *dopt, TableInfo *tbinfo, bool oids)
dopt->no_unlogged_table_data)
return;
+ /* Don't dump data of variables */
+ if (tbinfo->relkind == RELKIND_VARIABLE)
+ return;
+
/* Check that the data is not explicitly excluded */
if (simple_oid_list_member(&tabledata_exclude_oids,
tbinfo->dobj.catId.oid))
@@ -2498,9 +2503,10 @@ guessConstraintInheritance(TableInfo *tblinfo, int numTables)
TableInfo **parents;
TableInfo *parent;
- /* Sequences and views never have parents */
+ /* Sequences, variables and views never have parents */
if (tbinfo->relkind == RELKIND_SEQUENCE ||
- tbinfo->relkind == RELKIND_VIEW)
+ tbinfo->relkind == RELKIND_VIEW ||
+ tbinfo->relkind == RELKIND_VARIABLE)
continue;
/* Don't bother computing anything for non-target tables, either */
@@ -5970,7 +5976,7 @@ getTables(Archive *fout, int *numTables)
"(c.oid = pip.objoid "
"AND pip.classoid = 'pg_class'::regclass "
"AND pip.objsubid = 0) "
- "WHERE c.relkind in ('%c', '%c', '%c', '%c', '%c', '%c', '%c') "
+ "WHERE c.relkind in ('%c', '%c', '%c', '%c', '%c', '%c', '%c', '%c') "
"ORDER BY c.oid",
acl_subquery->data,
racl_subquery->data,
@@ -5989,7 +5995,8 @@ getTables(Archive *fout, int *numTables)
RELKIND_RELATION, RELKIND_SEQUENCE,
RELKIND_VIEW, RELKIND_COMPOSITE_TYPE,
RELKIND_MATVIEW, RELKIND_FOREIGN_TABLE,
- RELKIND_PARTITIONED_TABLE);
+ RELKIND_PARTITIONED_TABLE,
+ RELKIND_VARIABLE);
destroyPQExpBuffer(acl_subquery);
destroyPQExpBuffer(racl_subquery);
@@ -15645,6 +15652,11 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo)
srvname = NULL;
ftoptions = NULL;
break;
+ case RELKIND_VARIABLE:
+ reltypename = "VARIABLE";
+ srvname = NULL;
+ ftoptions = NULL;
+ break;
default:
reltypename = "TABLE";
srvname = NULL;
@@ -15676,12 +15688,23 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo)
reltypename,
fmtId(tbinfo->dobj.name));
+ /*
+ * Binary upgrade of variables should to be fixed with support
+ * composite types, and should be tested ToDo
+ */
+ if (tbinfo->relkind == RELKIND_VARIABLE)
+ {
+ appendPQExpBuffer(q, " AS %s",
+ tbinfo->reloftype);
+ }
+
/*
* Attach to type, if reloftype; except in case of a binary upgrade,
* we dump the table normally and attach it to the type afterward.
*/
- if (tbinfo->reloftype && !dopt->binary_upgrade)
- appendPQExpBuffer(q, " OF %s", tbinfo->reloftype);
+ else if (tbinfo->reloftype && !dopt->binary_upgrade)
+ appendPQExpBuffer(q, " OF %s",
+ tbinfo->reloftype);
/*
* If the table is a partition, dump it as such; except in the case of
@@ -15837,7 +15860,8 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo)
* We must have a parenthesized attribute list, even though
* empty, when not using the OF TYPE or PARTITION OF syntax.
*/
- appendPQExpBufferStr(q, " (\n)");
+ if (tbinfo->relkind != RELKIND_VARIABLE)
+ appendPQExpBufferStr(q, " (\n)");
}
if (tbinfo->ispartition && !dopt->binary_upgrade)
diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h
index 6c18d451ef..bb846f64d7 100644
--- a/src/bin/pg_dump/pg_dump.h
+++ b/src/bin/pg_dump/pg_dump.h
@@ -84,7 +84,7 @@ typedef enum
DO_POLICY,
DO_PUBLICATION,
DO_PUBLICATION_REL,
- DO_SUBSCRIPTION
+ DO_SUBSCRIPTION,
} DumpableObjectType;
/* component types of an object which can be selected for dumping */
diff --git a/src/bin/pg_dump/pg_dump_sort.c b/src/bin/pg_dump/pg_dump_sort.c
index 5ce3c5d485..9a43512e67 100644
--- a/src/bin/pg_dump/pg_dump_sort.c
+++ b/src/bin/pg_dump/pg_dump_sort.c
@@ -1470,6 +1470,11 @@ describeDumpableObject(DumpableObject *obj, char *buf, int bufsize)
"POST-DATA BOUNDARY (ID %d)",
obj->dumpId);
return;
+ case DO_VARIABLE:
+ snprintf(buf, bufsize,
+ "VARIABLE %s (ID %d OID %u)",
+ obj->name, obj->dumpId, obj->catId.oid);
+ return;
}
/* shouldn't get here */
snprintf(buf, bufsize,
diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl
index 3e9b4d94dc..d10107162b 100644
--- a/src/bin/pg_dump/t/002_pg_dump.pl
+++ b/src/bin/pg_dump/t/002_pg_dump.pl
@@ -5341,6 +5341,38 @@ qr/CREATE TRANSFORM FOR integer LANGUAGE sql \(FROM SQL WITH FUNCTION pg_catalog
role => 1,
section_post_data => 1, }, },
+ 'CREATE VARIABLE test_variable' => {
+ all_runs => 1,
+ catch_all => 'CREATE ... commands',
+ create_order => 61,
+ create_sql => 'CREATE VARIABLE dump_test.variable AS integer;',
+ regexp => qr/^
+ \QCREATE VARIABLE variable AS integer;\E/xm,
+ like => {
+ binary_upgrade => 1,
+ clean => 1,
+ clean_if_exists => 1,
+ createdb => 1,
+ defaults => 1,
+ exclude_test_table => 1,
+ exclude_test_table_data => 1,
+ no_blobs => 1,
+ no_privs => 1,
+ no_owner => 1,
+ only_dump_test_schema => 1,
+ pg_dumpall_dbprivs => 1,
+ schema_only => 1,
+ section_pre_data => 1,
+ test_schema_plus_blobs => 1,
+ with_oids => 1, },
+ unlike => {
+ exclude_dump_test_schema => 1,
+ only_dump_test_table => 1,
+ pg_dumpall_globals => 1,
+ pg_dumpall_globals_clean => 1,
+ role => 1,
+ section_post_data => 1, }, },
+
'ALTER VIEW test_view SET DEFAULT' => {
all_runs => 1,
catch_all => 'CREATE ... commands',
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index 3560318749..ad0030c0dd 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -794,6 +794,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
case 'i':
case 's':
case 'E':
+ case 'V':
success = listTables(&cmd[1], pattern, show_verbose, show_system);
break;
case 'r':
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 466a78004b..c272de2baa 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -1683,6 +1683,42 @@ describeOneTableDetails(const char *schemaname,
retval = true;
goto error_return; /* not an error, just return early */
}
+ else if (tableinfo.relkind == RELKIND_VARIABLE)
+ {
+ PGresult *res = NULL;
+ printQueryOpt myopt = pset.popt;
+
+ printfPQExpBuffer(&buf,
+ "SELECT pg_catalog.format_type(reloftype, NULL) AS \"%s\"\n"
+ "FROM pg_catalog.pg_class\n"
+ "WHERE oid = '%s';",
+ gettext_noop("Type"),
+ oid);
+
+ res = PSQLexec(buf.data);
+ if (!res)
+ goto error_return;
+
+ /* Did we get anything? */
+ if (PQntuples(res) == 0)
+ {
+ if (!pset.quiet)
+ psql_error("Did not find any variable with OID %s.\n", oid);
+ goto error_return;
+ }
+
+ printfPQExpBuffer(&title, _("Schema variable \"%s.%s\""),
+ schemaname, relationname);
+
+ myopt.title = title.data;
+
+ printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
+
+ PQclear(res);
+
+ retval = true;
+ goto error_return; /* not an error, just return early */
+ }
/*
* Get column info
@@ -3365,6 +3401,7 @@ listDbRoleSettings(const char *pattern, const char *pattern2)
* m - materialized views
* s - sequences
* E - foreign table (Note: different from 'f', the relkind value)
+ * V - schema variable
* (any order of the above is fine)
*/
bool
@@ -3376,6 +3413,7 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
bool showMatViews = strchr(tabtypes, 'm') != NULL;
bool showSeq = strchr(tabtypes, 's') != NULL;
bool showForeign = strchr(tabtypes, 'E') != NULL;
+ bool showVariables = strchr(tabtypes, 'V') != NULL;
PQExpBufferData buf;
PGresult *res;
@@ -3383,8 +3421,8 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
static const bool translate_columns[] = {false, false, true, false, false, false, false};
/* If tabtypes is empty, we default to \dtvmsE (but see also command.c) */
- if (!(showTables || showIndexes || showViews || showMatViews || showSeq || showForeign))
- showTables = showViews = showMatViews = showSeq = showForeign = true;
+ if (!(showTables || showIndexes || showViews || showMatViews || showSeq || showForeign || showVariables))
+ showTables = showViews = showMatViews = showSeq = showForeign = showVariables = true;
initPQExpBuffer(&buf);
@@ -3405,6 +3443,7 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
" WHEN " CppAsString2(RELKIND_FOREIGN_TABLE) " THEN '%s'"
" WHEN " CppAsString2(RELKIND_PARTITIONED_TABLE) " THEN '%s'"
" WHEN " CppAsString2(RELKIND_PARTITIONED_INDEX) " THEN '%s'"
+ " WHEN " CppAsString2(RELKIND_VARIABLE) " THEN '%s'"
" END as \"%s\",\n"
" pg_catalog.pg_get_userbyid(c.relowner) as \"%s\"",
gettext_noop("Schema"),
@@ -3418,6 +3457,7 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
gettext_noop("foreign table"),
gettext_noop("table"), /* partitioned table */
gettext_noop("index"), /* partitioned index */
+ gettext_noop("schema variable"),
gettext_noop("Type"),
gettext_noop("Owner"));
@@ -3471,6 +3511,8 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
appendPQExpBufferStr(&buf, "'s',"); /* was RELKIND_SPECIAL */
if (showForeign)
appendPQExpBufferStr(&buf, CppAsString2(RELKIND_FOREIGN_TABLE) ",");
+ if (showVariables)
+ appendPQExpBufferStr(&buf, CppAsString2(RELKIND_VARIABLE) ",");
appendPQExpBufferStr(&buf, "''"); /* dummy */
appendPQExpBufferStr(&buf, ")\n");
diff --git a/src/bin/psql/describe.h b/src/bin/psql/describe.h
index a4cc5efae0..c5f107d814 100644
--- a/src/bin/psql/describe.h
+++ b/src/bin/psql/describe.h
@@ -60,7 +60,7 @@ extern bool listTSTemplates(const char *pattern, bool verbose);
/* \l */
extern bool listAllDbs(const char *pattern, bool verbose);
-/* \dt, \di, \ds, \dS, etc. */
+/* \dt, \di, \ds, \dS, \dvar etc. */
extern bool listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSystem);
/* \dD */
diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c
index 702e742af4..2da50f7290 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -167,7 +167,7 @@ slashUsage(unsigned short int pager)
* Use "psql --help=commands | wc" to count correctly. It's okay to count
* the USE_READLINE line even in builds without that.
*/
- output = PageOutput(125, pager ? &(pset.popt.topt) : NULL);
+ output = PageOutput(126, pager ? &(pset.popt.topt) : NULL);
fprintf(output, _("General\n"));
fprintf(output, _(" \\copyright show PostgreSQL usage and distribution terms\n"));
@@ -257,6 +257,7 @@ slashUsage(unsigned short int pager)
fprintf(output, _(" \\dT[S+] [PATTERN] list data types\n"));
fprintf(output, _(" \\du[S+] [PATTERN] list roles\n"));
fprintf(output, _(" \\dv[S+] [PATTERN] list views\n"));
+ fprintf(output, _(" \\dV[S+] [PATTERN] list schema variables\n"));
fprintf(output, _(" \\dx[+] [PATTERN] list extensions\n"));
fprintf(output, _(" \\dy [PATTERN] list event triggers\n"));
fprintf(output, _(" \\l[+] [PATTERN] list databases\n"));
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 8bc4a194a5..ba5f6b0832 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -679,6 +679,20 @@ static const SchemaQuery Query_for_list_of_statistics = {
NULL
};
+static const SchemaQuery Query_for_list_of_variables = {
+ /* catname */
+ "pg_catalog.pg_class c",
+ /* selcondition */
+ "c.relkind IN ('V')",
+ /* viscondition */
+ "pg_catalog.pg_table_is_visible(c.oid)",
+ /* namespace */
+ "c.relnamespace",
+ /* result */
+ "pg_catalog.quote_ident(c.relname)",
+ /* qualresult */
+ NULL
+};
/*
* Queries to get lists of names of various kinds of things, possibly
@@ -1108,6 +1122,7 @@ static const pgsql_thing_t words_after_create[] = {
* TABLE ... */
{"USER", Query_for_list_of_roles " UNION SELECT 'MAPPING FOR'"},
{"USER MAPPING FOR", NULL, NULL},
+ {"VARIABLE", NULL, &Query_for_list_of_variables},
{"VIEW", NULL, &Query_for_list_of_views},
{NULL} /* end of list */
};
@@ -1460,7 +1475,7 @@ psql_completion(const char *text, int start, int end)
"ABORT", "ALTER", "ANALYZE", "BEGIN", "CALL", "CHECKPOINT", "CLOSE", "CLUSTER",
"COMMENT", "COMMIT", "COPY", "CREATE", "DEALLOCATE", "DECLARE",
"DELETE FROM", "DISCARD", "DO", "DROP", "END", "EXECUTE", "EXPLAIN",
- "FETCH", "GRANT", "IMPORT", "INSERT", "LISTEN", "LOAD", "LOCK",
+ "FETCH", "GRANT", "IMPORT", "INSERT", "LET", "LISTEN", "LOAD", "LOCK",
"MOVE", "NOTIFY", "PREPARE",
"REASSIGN", "REFRESH MATERIALIZED VIEW", "REINDEX", "RELEASE",
"RESET", "REVOKE", "ROLLBACK",
@@ -1479,7 +1494,7 @@ psql_completion(const char *text, int start, int end)
"\\dF", "\\dFd", "\\dFp", "\\dFt", "\\dg", "\\di", "\\dl", "\\dL",
"\\dm", "\\dn", "\\do", "\\dO", "\\dp",
"\\drds", "\\dRs", "\\dRp", "\\ds", "\\dS",
- "\\dt", "\\dT", "\\dv", "\\du", "\\dx", "\\dy",
+ "\\dt", "\\dT", "\\dv", "\\du", "\\dx", "\\dy", "\\dvar",
"\\e", "\\echo", "\\ef", "\\elif", "\\else", "\\encoding",
"\\endif", "\\errverbose", "\\ev",
"\\f",
@@ -2684,6 +2699,14 @@ psql_completion(const char *text, int start, int end)
else if (Matches4("CREATE", "ROLE|USER|GROUP", MatchAny, "IN"))
COMPLETE_WITH_LIST2("GROUP", "ROLE");
+/* CREATE VARIABLE --- is allowed inside CREATE SCHEMA, so use TailMatches */
+ /* Complete CREATE VARIABLE with AS */
+ else if (TailMatches3("CREATE", "VARIABLE", MatchAny))
+ COMPLETE_WITH_CONST("AS");
+ /* Complete CREATE VARIABLE with AS types*/
+ else if (TailMatches4("CREATE", "VARIABLE", MatchAny, "AS"))
+ COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_datatypes, NULL);
+
/* CREATE VIEW --- is allowed inside CREATE SCHEMA, so use TailMatches */
/* Complete CREATE VIEW with AS */
else if (TailMatches3("CREATE", "VIEW", MatchAny))
@@ -2839,6 +2862,12 @@ psql_completion(const char *text, int start, int end)
else if (Matches5("DROP", "RULE", MatchAny, "ON", MatchAny))
COMPLETE_WITH_LIST2("CASCADE", "RESTRICT");
+ /* DROP VARIABLE */
+ else if (Matches2("DROP", "VARIABLE"))
+ COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_variables, NULL);
+ else if (Matches3("DROP", "VARIABLE", MatchAny))
+ COMPLETE_WITH_LIST2("CASCADE", "RESTRICT");
+
/* EXECUTE */
else if (Matches1("EXECUTE"))
COMPLETE_WITH_QUERY(Query_for_list_of_prepared_statements);
@@ -2849,14 +2878,14 @@ psql_completion(const char *text, int start, int end)
* Complete EXPLAIN [ANALYZE] [VERBOSE] with list of EXPLAIN-able commands
*/
else if (Matches1("EXPLAIN"))
- COMPLETE_WITH_LIST7("SELECT", "INSERT", "DELETE", "UPDATE", "DECLARE",
- "ANALYZE", "VERBOSE");
+ COMPLETE_WITH_LIST8("SELECT", "INSERT", "DELETE", "UPDATE", "DECLARE",
+ "ANALYZE", "VERBOSE", "LET");
else if (Matches2("EXPLAIN", "ANALYZE"))
- COMPLETE_WITH_LIST6("SELECT", "INSERT", "DELETE", "UPDATE", "DECLARE",
- "VERBOSE");
+ COMPLETE_WITH_LIST7("SELECT", "INSERT", "DELETE", "UPDATE", "DECLARE",
+ "VERBOSE", "LET");
else if (Matches2("EXPLAIN", "VERBOSE") ||
Matches3("EXPLAIN", "ANALYZE", "VERBOSE"))
- COMPLETE_WITH_LIST5("SELECT", "INSERT", "DELETE", "UPDATE", "DECLARE");
+ COMPLETE_WITH_LIST6("SELECT", "INSERT", "DELETE", "UPDATE", "DECLARE", "LET");
/* FETCH && MOVE */
/* Complete FETCH with one of FORWARD, BACKWARD, RELATIVE */
@@ -2965,6 +2994,7 @@ psql_completion(const char *text, int start, int end)
" UNION SELECT 'ALL ROUTINES IN SCHEMA'"
" UNION SELECT 'ALL SEQUENCES IN SCHEMA'"
" UNION SELECT 'ALL TABLES IN SCHEMA'"
+ " UNION SELECT 'ALL VARIABLES IN SCHEMA'"
" UNION SELECT 'DATABASE'"
" UNION SELECT 'DOMAIN'"
" UNION SELECT 'FOREIGN DATA WRAPPER'"
@@ -2978,14 +3008,16 @@ psql_completion(const char *text, int start, int end)
" UNION SELECT 'SEQUENCE'"
" UNION SELECT 'TABLE'"
" UNION SELECT 'TABLESPACE'"
- " UNION SELECT 'TYPE'");
+ " UNION SELECT 'TYPE'"
+ " UNION SELECT 'VARIABLE'");
}
else if (TailMatches4("GRANT|REVOKE", MatchAny, "ON", "ALL"))
- COMPLETE_WITH_LIST5("FUNCTIONS IN SCHEMA",
+ COMPLETE_WITH_LIST6("FUNCTIONS IN SCHEMA",
"PROCEDURES IN SCHEMA",
"ROUTINES IN SCHEMA",
"SEQUENCES IN SCHEMA",
- "TABLES IN SCHEMA");
+ "TABLES IN SCHEMA",
+ "VARIABLES IN SCHEMA");
else if (TailMatches4("GRANT|REVOKE", MatchAny, "ON", "FOREIGN"))
COMPLETE_WITH_LIST2("DATA WRAPPER", "SERVER");
@@ -3015,6 +3047,8 @@ psql_completion(const char *text, int start, int end)
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_sequences, NULL);
else if (TailMatches1("TABLE"))
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tsvmf, NULL);
+ else if (TailMatches1("VARIABLE"))
+ COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_variables, NULL);
else if (TailMatches1("TABLESPACE"))
COMPLETE_WITH_QUERY(Query_for_list_of_tablespaces);
else if (TailMatches1("TYPE"))
@@ -3171,7 +3205,7 @@ psql_completion(const char *text, int start, int end)
/* PREPARE xx AS */
else if (Matches3("PREPARE", MatchAny, "AS"))
- COMPLETE_WITH_LIST4("SELECT", "UPDATE", "INSERT", "DELETE FROM");
+ COMPLETE_WITH_LIST5("SELECT", "UPDATE", "INSERT", "DELETE FROM", "LET");
/*
* PREPARE TRANSACTION is missing on purpose. It's intended for transaction
@@ -3390,6 +3424,14 @@ psql_completion(const char *text, int start, int end)
else if (TailMatches4("UPDATE", MatchAny, "SET", MatchAny))
COMPLETE_WITH_CONST("=");
+/* LET --- can be inside EXPLAIN, PREPARE etc */
+ /* If prev. word is LET suggest a list of variables */
+ else if (TailMatches1("LET"))
+ COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_variables, NULL);
+ /* Complete LET with "=" */
+ else if (TailMatches2("LET", MatchAny))
+ COMPLETE_WITH_CONST("=");
+
/* USER MAPPING */
else if (Matches3("ALTER|CREATE|DROP", "USER", "MAPPING"))
COMPLETE_WITH_CONST("FOR");
diff --git a/src/include/catalog/pg_class.h b/src/include/catalog/pg_class.h
index 26b1866c69..c5146fc138 100644
--- a/src/include/catalog/pg_class.h
+++ b/src/include/catalog/pg_class.h
@@ -167,6 +167,7 @@ DESCR("");
#define RELKIND_FOREIGN_TABLE 'f' /* foreign table */
#define RELKIND_PARTITIONED_TABLE 'p' /* partitioned table */
#define RELKIND_PARTITIONED_INDEX 'I' /* partitioned index */
+#define RELKIND_VARIABLE 'V' /* schema variable */
#define RELPERSISTENCE_PERMANENT 'p' /* regular table */
#define RELPERSISTENCE_UNLOGGED 'u' /* unlogged permanent table */
diff --git a/src/include/catalog/pg_default_acl.h b/src/include/catalog/pg_default_acl.h
index 11b306037d..13232d7a43 100644
--- a/src/include/catalog/pg_default_acl.h
+++ b/src/include/catalog/pg_default_acl.h
@@ -71,5 +71,6 @@ typedef FormData_pg_default_acl *Form_pg_default_acl;
#define DEFACLOBJ_FUNCTION 'f' /* function */
#define DEFACLOBJ_TYPE 'T' /* type */
#define DEFACLOBJ_NAMESPACE 'n' /* namespace */
+#define DEFACLOBJ_VARIABLE 'V' /* variable */
#endif /* PG_DEFAULT_ACL_H */
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index 2a5321315a..2f79bbe89c 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -5561,6 +5561,12 @@ DESCR("list of files in the WAL directory");
DATA(insert OID = 5028 ( satisfies_hash_partition PGNSP PGUID 12 1 0 2276 0 f f f f f f i s 4 0 16 "26 23 23 2276" _null_ "{i,i,i,v}" _null_ _null_ _null_ satisfies_hash_partition _null_ _null_ _null_ ));
DESCR("hash partition CHECK constraint");
+/* schema variables function interface */
+DATA(insert OID = 6122 ( get_schema_variable PGNSP PGUID 12 1 0 0 0 f f f f f f v r 2 0 2283 "2205 2283" _null_ _null_ _null_ _null_ _null_ get_schema_variable _null_ _null_ _null_ ));
+DESCR("returns value of schema variable");
+DATA(insert OID = 6123 ( set_schema_variable PGNSP PGUID 12 1 0 0 0 f f f f f f v r 2 0 2278 "2205 2283" _null_ _null_ _null_ _null_ _null_ set_schema_variable _null_ _null_ _null_ ));
+DESCR("returns value of schema variable");
+
/*
* Symbolic values for provolatile column: these indicate whether the result
* of a function is dependent *only* on the values of its explicit arguments,
diff --git a/src/include/commands/schemavar.h b/src/include/commands/schemavar.h
new file mode 100644
index 0000000000..6f65b1f1d3
--- /dev/null
+++ b/src/include/commands/schemavar.h
@@ -0,0 +1,31 @@
+/*-------------------------------------------------------------------------
+ *
+ * schemavar.h
+ * prototypes for schemavar.c.
+ *
+ * Portions Copyright (c) 1996-2018, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994, Regents of the University of California
+ *
+ * src/include/commands/schemavar.h
+ *
+ *-------------------------------------------------------------------------
+ */
+#ifndef SCHEMAVAR_H
+#define SCHEMAVAR_H
+
+#include "catalog/objectaddress.h"
+#include "nodes/parsenodes.h"
+#include "parser/parse_node.h"
+
+extern ObjectAddress DefineSchemaVariable(ParseState *pstate, CreateSchemaVarStmt *var);
+
+extern void ResetSchemaVariablesCache(void);
+
+extern char *get_schemavar_name(Oid varid);
+
+extern void SetSchemaVariable(Oid varid, Datum value, bool isNull, Oid typid, int32 typmod, int16 typlen, bool typbyval);
+extern Datum GetSchemaVariable(Oid varid, bool *isNull, Oid typid, int32 typmod, int16 typlen, bool typbyval);
+extern void SetSchemaVariableSecure(Oid varid, Datum value, bool isNull, Oid typid, int32 typmod, int16 typlen, bool typbyval);
+extern Datum GetSchemaVariableSecure(Oid varid, bool *isNull, Oid typid, int32 typmod, int16 typlen, bool typbyval);
+
+#endif
\ No newline at end of file
diff --git a/src/include/executor/execExpr.h b/src/include/executor/execExpr.h
index 117fc892f4..a282f1e4e0 100644
--- a/src/include/executor/execExpr.h
+++ b/src/include/executor/execExpr.h
@@ -138,6 +138,7 @@ typedef enum ExprEvalOp
EEOP_PARAM_EXEC,
EEOP_PARAM_EXTERN,
EEOP_PARAM_CALLBACK,
+ EEOP_PARAM_SCHEMA_VARIABLE,
/* return CaseTestExpr value */
EEOP_CASE_TESTVAL,
@@ -342,11 +343,14 @@ typedef struct ExprEvalStep
TupleDesc argdesc;
} nulltest_row;
- /* for EEOP_PARAM_EXEC/EXTERN */
+ /* for EEOP_PARAM_EXEC/EXTERN/VARIABLE */
struct
{
- int paramid; /* numeric ID for parameter */
- Oid paramtype; /* OID of parameter's datatype */
+ int paramid; /* numeric ID for parameter */
+ Oid paramtype; /* OID of parameter's datatype */
+ int32 paramtypmod; /* typmod of param (not used yet) */
+ int16 paramtyplen; /* expected length */
+ bool paramtypbyval; /* is passed by value */
} param;
/* for EEOP_PARAM_CALLBACK */
diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h
index 74b094a9c3..2f4986099d 100644
--- a/src/include/nodes/nodes.h
+++ b/src/include/nodes/nodes.h
@@ -344,6 +344,7 @@ typedef enum NodeTag
T_CreateTableAsStmt,
T_CreateSeqStmt,
T_AlterSeqStmt,
+ T_CreateSchemaVarStmt,
T_VariableSetStmt,
T_VariableShowStmt,
T_DiscardStmt,
@@ -415,6 +416,7 @@ typedef enum NodeTag
T_CreateStatsStmt,
T_AlterCollationStmt,
T_CallStmt,
+ T_LetStmt,
/*
* TAGS FOR PARSE TREE NODES (parsenodes.h)
@@ -657,6 +659,7 @@ typedef enum CmdType
CMD_UPDATE, /* update stmt */
CMD_INSERT, /* insert stmt */
CMD_DELETE,
+ CMD_LET,
CMD_UTILITY, /* cmds like create, destroy, copy, vacuum,
* etc. */
CMD_NOTHING /* dummy command for instead nothing rules
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index c7a43b8933..8e65b3726c 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -1502,6 +1502,14 @@ typedef struct UpdateStmt
WithClause *withClause; /* WITH clause */
} UpdateStmt;
+typedef struct LetStmt
+{
+ NodeTag type;
+ RangeVar *variable; /* relation to insert into */
+ Node *selectStmt; /* the source SELECT/VALUES, or NULL */
+} LetStmt;
+
+
/* ----------------------
* Select Statement
*
@@ -1679,6 +1687,7 @@ typedef enum ObjectType
OBJECT_TSTEMPLATE,
OBJECT_TYPE,
OBJECT_USER_MAPPING,
+ OBJECT_VARIABLE,
OBJECT_VIEW
} ObjectType;
@@ -2491,6 +2500,18 @@ typedef struct AlterSeqStmt
bool missing_ok; /* skip error if a role is missing? */
} AlterSeqStmt;
+/* ----------------------
+ * Create VARIABLE Statement
+ * ----------------------
+ */
+typedef struct CreateSchemaVarStmt
+{
+ NodeTag type;
+ RangeVar *variable; /* the variable to create */
+ TypeName *typeName; /* the variable type */
+ bool if_not_exists; /* just do nothing if it already exists? */
+} CreateSchemaVarStmt;
+
/* ----------------------
* Create {Aggregate|Operator|Type} Statement
* ----------------------
@@ -3220,7 +3241,8 @@ typedef enum DiscardMode
DISCARD_ALL,
DISCARD_PLANS,
DISCARD_SEQUENCES,
- DISCARD_TEMP
+ DISCARD_TEMP,
+ DISCARD_VARIABLES
} DiscardMode;
typedef struct DiscardStmt
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index 1b4b0d75af..b366471940 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -229,13 +229,17 @@ typedef struct Const
* of the `paramid' field contain the SubLink's subLinkId, and
* the low-order 16 bits contain the column number. (This type
* of Param is also converted to PARAM_EXEC during planning.)
+ *
+ * PARAM_SCHEMA_VARIABLE: The parameter is a access to schema variable
+ * paramid holds varid.
*/
typedef enum ParamKind
{
PARAM_EXTERN,
PARAM_EXEC,
PARAM_SUBLINK,
- PARAM_MULTIEXPR
+ PARAM_MULTIEXPR,
+ PARAM_SCHEMA_VARIABLE
} ParamKind;
typedef struct Param
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index cf32197bc3..db11e087c1 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -230,6 +230,7 @@ PG_KEYWORD("leading", LEADING, RESERVED_KEYWORD)
PG_KEYWORD("leakproof", LEAKPROOF, UNRESERVED_KEYWORD)
PG_KEYWORD("least", LEAST, COL_NAME_KEYWORD)
PG_KEYWORD("left", LEFT, TYPE_FUNC_NAME_KEYWORD)
+PG_KEYWORD("let", LET, UNRESERVED_KEYWORD)
PG_KEYWORD("level", LEVEL, UNRESERVED_KEYWORD)
PG_KEYWORD("like", LIKE, TYPE_FUNC_NAME_KEYWORD)
PG_KEYWORD("limit", LIMIT, RESERVED_KEYWORD)
@@ -433,6 +434,8 @@ PG_KEYWORD("validator", VALIDATOR, UNRESERVED_KEYWORD)
PG_KEYWORD("value", VALUE_P, UNRESERVED_KEYWORD)
PG_KEYWORD("values", VALUES, COL_NAME_KEYWORD)
PG_KEYWORD("varchar", VARCHAR, COL_NAME_KEYWORD)
+PG_KEYWORD("variable", VARIABLE, UNRESERVED_KEYWORD)
+PG_KEYWORD("variables", VARIABLES, UNRESERVED_KEYWORD)
PG_KEYWORD("variadic", VARIADIC, RESERVED_KEYWORD)
PG_KEYWORD("varying", VARYING, UNRESERVED_KEYWORD)
PG_KEYWORD("verbose", VERBOSE, TYPE_FUNC_NAME_KEYWORD)
diff --git a/src/include/parser/parse_node.h b/src/include/parser/parse_node.h
index 2e0792d60b..2e7c54a970 100644
--- a/src/include/parser/parse_node.h
+++ b/src/include/parser/parse_node.h
@@ -135,6 +135,8 @@ typedef Node *(*CoerceParamHook) (ParseState *pstate, Param *param,
* to process them like UPDATE. (Note this can change intra-statement, for
* cases like INSERT ON CONFLICT UPDATE.)
*
+ * p_is_let: true to process assignment expressions like LET.
+ *
* p_windowdefs: list of WindowDefs representing WINDOW and OVER clauses.
* We collect these while transforming expressions and then transform them
* afterwards (so that any resjunk tlist items needed for the sort/group
@@ -184,6 +186,7 @@ struct ParseState
Relation p_target_relation; /* INSERT/UPDATE/DELETE target rel */
RangeTblEntry *p_target_rangetblentry; /* target rel's RTE */
bool p_is_insert; /* process assignment like INSERT not UPDATE */
+ bool p_is_let; /* process assignment LET stmt */
List *p_windowdefs; /* raw representations of window clauses */
ParseExprKind p_expr_kind; /* what kind of expression we're parsing */
int p_next_resno; /* next targetlist resno to assign */
diff --git a/src/include/parser/parse_relation.h b/src/include/parser/parse_relation.h
index b9792acdae..760aaed9a8 100644
--- a/src/include/parser/parse_relation.h
+++ b/src/include/parser/parse_relation.h
@@ -129,4 +129,6 @@ extern Oid attnumTypeId(Relation rd, int attid);
extern Oid attnumCollationId(Relation rd, int attid);
extern bool isQueryUsingTempRelation(Query *query);
+extern Node *toSchemaVariable(ParseState *pstate, char *nspname, char *varname, int location);
+
#endif /* PARSE_RELATION_H */
diff --git a/src/include/utils/acl.h b/src/include/utils/acl.h
index f4d4be8d0d..d0737a9e4b 100644
--- a/src/include/utils/acl.h
+++ b/src/include/utils/acl.h
@@ -166,6 +166,7 @@ typedef ArrayType Acl;
#define ACL_ALL_RIGHTS_SCHEMA (ACL_USAGE|ACL_CREATE)
#define ACL_ALL_RIGHTS_TABLESPACE (ACL_CREATE)
#define ACL_ALL_RIGHTS_TYPE (ACL_USAGE)
+#define ACL_ALL_RIGHTS_VARIABLE (ACL_SELECT|ACL_UPDATE)
/* operation codes for pg_*_aclmask */
typedef enum
diff --git a/src/test/regress/expected/schema_variables.out b/src/test/regress/expected/schema_variables.out
new file mode 100644
index 0000000000..ad700c15d8
--- /dev/null
+++ b/src/test/regress/expected/schema_variables.out
@@ -0,0 +1,236 @@
+CREATE VARIABLE var1 AS integer;
+CREATE TEMP VARIABLE var2 AS text;
+-- should to fail
+CREATE VARIABLE var2 AS pg_class;
+ERROR: Composite types are not allowed as variable type.
+DROP VARIABLE var1, var2;
+-- functional interface, attention typmod is not stored
+CREATE VARIABLE var1 AS numeric(10,1);
+SELECT set_schema_variable('var1', 333);
+ set_schema_variable
+---------------------
+
+(1 row)
+
+SELECT get_schema_variable('var1', null::numeric);
+ get_schema_variable
+---------------------
+ 333
+(1 row)
+
+SELECT set_schema_variable('var1', 333::integer);
+ set_schema_variable
+---------------------
+
+(1 row)
+
+SELECT get_schema_variable('var1', null::numeric);
+ get_schema_variable
+---------------------
+ 333
+(1 row)
+
+SELECT set_schema_variable('var1', '333.55'::text);
+ set_schema_variable
+---------------------
+
+(1 row)
+
+SELECT get_schema_variable('var1', null::numeric);
+ get_schema_variable
+---------------------
+ 333.55
+(1 row)
+
+SELECT get_schema_variable('var1', null::int);
+ get_schema_variable
+---------------------
+ 334
+(1 row)
+
+SELECT get_schema_variable('var1', null::text);
+ get_schema_variable
+---------------------
+ 333.55
+(1 row)
+
+-- access rights test
+CREATE ROLE var_test_role;
+SET ROLE TO var_test_role;
+-- should to fail
+SELECT set_schema_variable('var1', '1000'::text);
+ERROR: permission denied for schema variable var1
+SELECT get_schema_variable('var1', null::numeric);
+ERROR: permission denied for schema variable var1
+SET ROLE TO DEFAULT;
+GRANT SELECT ON VARIABLE var1 TO var_test_role;
+SET ROLE TO var_test_role;
+-- should to fail
+SELECT set_schema_variable('var1', '1000'::text);
+ERROR: permission denied for schema variable var1
+-- should to work
+SELECT get_schema_variable('var1', null::numeric);
+ get_schema_variable
+---------------------
+ 333.55
+(1 row)
+
+SET ROLE TO DEFAULT;
+GRANT UPDATE ON VARIABLE var1 TO var_test_role;
+SET ROLE TO var_test_role;
+-- should to work
+SELECT set_schema_variable('var1', '1000'::text);
+ set_schema_variable
+---------------------
+
+(1 row)
+
+SELECT get_schema_variable('var1', null::numeric);
+ get_schema_variable
+---------------------
+ 1000
+(1 row)
+
+SET ROLE TO DEFAULT;
+REVOKE ALL ON VARIABLE var1 FROM var_test_role;
+CREATE VARIABLE var AS integer;
+SELECT set_schema_variable('public.var', 1234);
+ set_schema_variable
+---------------------
+
+(1 row)
+
+SELECT public.var;
+ var
+------
+ 1234
+(1 row)
+
+DO $$
+BEGIN
+ RAISE NOTICE 'public.var is = %', public.var;
+END;
+$$;
+NOTICE: public.var is = 1234
+CREATE OR REPLACE FUNCTION secure_var()
+RETURNS int AS $$
+ SELECT public.var;
+$$ LANGUAGE sql SECURITY DEFINER;
+SELECT secure_var();
+ secure_var
+------------
+ 1234
+(1 row)
+
+SET ROLE TO var_test_role;
+-- should to fail
+SELECT public.var;
+ERROR: permission denied for schema variable var
+-- should to work;
+SELECT secure_var();
+ secure_var
+------------
+ 1234
+(1 row)
+
+SET ROLE TO DEFAULT;
+EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM generate_series(1,100) g(v) WHERE v = var;
+ QUERY PLAN
+-----------------------------------------------
+ Function Scan on pg_catalog.generate_series g
+ Output: v
+ Function Call: generate_series(1, 100)
+ Filter: (g.v = var)
+(4 rows)
+
+CREATE VIEW schema_var_view AS SELECT var;
+SELECT * FROM schema_var_view;
+ var
+------
+ 1234
+(1 row)
+
+\c -
+-- should to work still, but var will be empty
+SELECT * FROM schema_var_view;
+ var
+-----
+
+(1 row)
+
+LET var1 = pi();
+SELECT var1;
+ var1
+------------------
+ 3.14159265358979
+(1 row)
+
+-- we can look on execution plan
+EXPLAIN (VERBOSE, COSTS OFF) LET var1 = pi();
+ QUERY PLAN
+------------------------------------------------------
+ Let on public.var1
+ -> Result
+ Output: '3.14159265358979'::double precision
+(3 rows)
+
+-- LET can be prepared
+PREPARE var_pp(int, numeric) AS LET var1 = $1 + $2;
+EXECUTE var_pp(100, 1.23456);
+SELECT var1;
+ var1
+-----------
+ 101.23456
+(1 row)
+
+CREATE VARIABLE var3 AS int;
+CREATE OR REPLACE FUNCTION inc(int)
+RETURNS int AS $$
+BEGIN
+ LET public.var3 = COALESCE(public.var3 + $1, $1);
+ RETURN var3;
+END;
+$$ LANGUAGE plpgsql;
+SELECT inc(1);
+ inc
+-----
+ 1
+(1 row)
+
+SELECT inc(1);
+ inc
+-----
+ 2
+(1 row)
+
+SELECT inc(1);
+ inc
+-----
+ 3
+(1 row)
+
+SELECT inc(1) FROM generate_series(1,10);
+ inc
+-----
+ 4
+ 5
+ 6
+ 7
+ 8
+ 9
+ 10
+ 11
+ 12
+ 13
+(10 rows)
+
+SET ROLE TO var_test_role;
+-- should to fail
+LET var3 = 0;
+ERROR: permission denied for schema variable var3
+SET ROLE TO DEFAULT;
+DROP VIEW schema_var_view;
+DROP ROLE var_test_role;
+DROP VARIABLE var CASCADE;
+DROP VARIABLE var1 CASCADE;
+DROP VARIABLE var3 CASCADE;
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index ad9434fb87..33fe7ee476 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -111,7 +111,7 @@ test: select_views portals_p2 foreign_key cluster dependency guc bitmapops combo
# NB: temp.sql does a reconnect which transiently uses 2 connections,
# so keep this parallel group to at most 19 tests
# ----------
-test: plancache limit plpgsql copy2 temp domain rangefuncs prepare without_oid conversion truncate alter_table sequence polymorphism rowtypes returning largeobject with xml
+test: plancache limit plpgsql copy2 temp domain rangefuncs prepare without_oid conversion truncate alter_table sequence polymorphism rowtypes returning largeobject with xml schema_variables
# ----------
# Another group of parallel tests
diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule
index 27cd49845e..22c4cac7ce 100644
--- a/src/test/regress/serial_schedule
+++ b/src/test/regress/serial_schedule
@@ -187,3 +187,4 @@ test: hash_part
test: indexing
test: event_trigger
test: stats
+test: schema_variables
diff --git a/src/test/regress/sql/schema_variables.sql b/src/test/regress/sql/schema_variables.sql
new file mode 100644
index 0000000000..9ee9e174f9
--- /dev/null
+++ b/src/test/regress/sql/schema_variables.sql
@@ -0,0 +1,139 @@
+CREATE VARIABLE var1 AS integer;
+CREATE TEMP VARIABLE var2 AS text;
+
+-- should to fail
+CREATE VARIABLE var2 AS pg_class;
+
+DROP VARIABLE var1, var2;
+
+-- functional interface, attention typmod is not stored
+CREATE VARIABLE var1 AS numeric(10,1);
+SELECT set_schema_variable('var1', 333);
+SELECT get_schema_variable('var1', null::numeric);
+
+SELECT set_schema_variable('var1', 333::integer);
+SELECT get_schema_variable('var1', null::numeric);
+
+SELECT set_schema_variable('var1', '333.55'::text);
+SELECT get_schema_variable('var1', null::numeric);
+SELECT get_schema_variable('var1', null::int);
+SELECT get_schema_variable('var1', null::text);
+
+-- access rights test
+
+CREATE ROLE var_test_role;
+
+SET ROLE TO var_test_role;
+
+-- should to fail
+SELECT set_schema_variable('var1', '1000'::text);
+SELECT get_schema_variable('var1', null::numeric);
+
+SET ROLE TO DEFAULT;
+
+GRANT SELECT ON VARIABLE var1 TO var_test_role;
+
+SET ROLE TO var_test_role;
+-- should to fail
+SELECT set_schema_variable('var1', '1000'::text);
+-- should to work
+SELECT get_schema_variable('var1', null::numeric);
+
+SET ROLE TO DEFAULT;
+
+GRANT UPDATE ON VARIABLE var1 TO var_test_role;
+
+SET ROLE TO var_test_role;
+
+-- should to work
+SELECT set_schema_variable('var1', '1000'::text);
+SELECT get_schema_variable('var1', null::numeric);
+
+SET ROLE TO DEFAULT;
+
+REVOKE ALL ON VARIABLE var1 FROM var_test_role;
+
+CREATE VARIABLE var AS integer;
+
+SELECT set_schema_variable('public.var', 1234);
+
+SELECT public.var;
+
+DO $$
+BEGIN
+ RAISE NOTICE 'public.var is = %', public.var;
+END;
+$$;
+
+CREATE OR REPLACE FUNCTION secure_var()
+RETURNS int AS $$
+ SELECT public.var;
+$$ LANGUAGE sql SECURITY DEFINER;
+
+SELECT secure_var();
+
+SET ROLE TO var_test_role;
+
+-- should to fail
+SELECT public.var;
+
+-- should to work;
+SELECT secure_var();
+
+SET ROLE TO DEFAULT;
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM generate_series(1,100) g(v) WHERE v = var;
+
+CREATE VIEW schema_var_view AS SELECT var;
+
+SELECT * FROM schema_var_view;
+
+\c -
+
+-- should to work still, but var will be empty
+SELECT * FROM schema_var_view;
+
+LET var1 = pi();
+
+SELECT var1;
+
+-- we can look on execution plan
+EXPLAIN (VERBOSE, COSTS OFF) LET var1 = pi();
+
+-- LET can be prepared
+PREPARE var_pp(int, numeric) AS LET var1 = $1 + $2;
+
+EXECUTE var_pp(100, 1.23456);
+
+SELECT var1;
+
+CREATE VARIABLE var3 AS int;
+
+CREATE OR REPLACE FUNCTION inc(int)
+RETURNS int AS $$
+BEGIN
+ LET public.var3 = COALESCE(public.var3 + $1, $1);
+ RETURN var3;
+END;
+$$ LANGUAGE plpgsql;
+
+SELECT inc(1);
+SELECT inc(1);
+SELECT inc(1);
+
+SELECT inc(1) FROM generate_series(1,10);
+
+SET ROLE TO var_test_role;
+
+-- should to fail
+LET var3 = 0;
+
+SET ROLE TO DEFAULT;
+
+DROP VIEW schema_var_view;
+
+DROP ROLE var_test_role;
+
+DROP VARIABLE var CASCADE;
+DROP VARIABLE var1 CASCADE;
+DROP VARIABLE var3 CASCADE;
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index d4765ce3b0..b0404c21a5 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -423,6 +423,7 @@ CreateReplicationSlotCmd
CreateRoleStmt
CreateSchemaStmt
CreateSchemaStmtContext
+CreateSchemaVarStmt
CreateSeqStmt
CreateStatsStmt
CreateStmt