The magic is called "row lock". The updates on the counter column are serialized by row locks, so there cannot be any race condition. A lost update can never happen if you read and write the data in a single UPDATE statement.

Assuming current Postgres 14 for lack of declaration. Index A https://www.postgresql.org/docs/current/indexes-multicolumn.html on (underlying_symbol, expiration_date) would make your query fast. Any variant will do as long as underlying_symbol is the leading column: equality first, range later. See: https://dba.stackexchange.com/questions/33196/multicolumn-index-and-performance/33220#33220 https://dba.stackexchange.com/questions/27481/is-a-composite-index-also-good-for-queries-on-the-first-field https://dba.stackexchange.com/questions/90128/unused-index-in-range-of-dates-query/90183#90183 Even a plain index on just (underlying_symbol) might go a long way - depending on undisclosed data distribution. Seeing that your table has very wide rows, a "covering" index might be ideal - if you get index-only scans out of it: CREATE INDEX foo ON cboe_raws (underlying_symbol, expiration_date DESC NULLS LAST) INCLUDE (strike_price, call_put_flag, mm_buy_vol, mm_sell_vol, mm_buy_vol); Function You typically don't need PL/pgSQL for a single, plain SQL query. Use an SQL function instead. I suggest the new standard-SQL variant: CREATE OR REPLACE FUNCTION market_maker_books(_symbol varchar) RETURNS TABLE (contract_underlying_symbol varchar , contract_expiration_date date , contract_strike_price float8 , contract_call_put_flag varchar , contract_mm_buy_vol_sum bigint , contract_mm_sell_vol_sum bigint , contract_buys_minus_sells bigint) LANGUAGE sql STABLE STRICT PARALLEL SAFE BEGIN ATOMIC SELECT c.underlying_symbol , c.expiration_date , c.strike_price , c.call_put_flag , SUM(c.mm_buy_vol) -- AS mm_buy_vol_sum , SUM(c.mm_sell_vol) -- AS mm_sell_vol_sum , SUM(c.mm_buy_vol) - SUM(c.mm_sell_vol) -- AS buys_minus_sells FROM cboe_raws c WHERE c.underlying_symbol = _symbol AND c.expiration_date >= CURRENT_DATE GROUP BY c.underlying_symbol, c.expiration_date, c.strike_price, c.call_put_flag ORDER BY c.expiration_date, c.call_put_flag, c.strike_price; END; See: https://dba.stackexchange.com/questions/300507/what-does-begin-atomic-end-mean-in-a-postgresql-sql-function-procedure/300512#300512 Might be untangled and compacted further: CREATE OR REPLACE FUNCTION market_maker_books(_symbol varchar) RETURNS TABLE (expiration_date date , call_put_flag varchar , strike_price float8 , mm_buy_vol_sum bigint , mm_sell_vol_sum bigint , buys_minus_sells bigint) LANGUAGE sql STABLE STRICT PARALLEL SAFE BEGIN ATOMIC SELECT c.expiration_date, c.call_put_flag, c.strike_price , SUM(c.mm_buy_vol) , SUM(c.mm_sell_vol) , SUM(c.mm_buy_vol) - SUM(c.mm_sell_vol) FROM cboe_raws c WHERE c.underlying_symbol = _symbol AND c.expiration_date >= CURRENT_DATE GROUP BY 1, 2, 3 ORDER BY 1, 2, 3; END;

You can use the AGE function. For more details check https://www.postgresql.org/docs/current/functions-datetime.html

This depends on the function. For a deterministic function specified with schemabinding, the optimizer will usually be able to evaluate its result just once, caching the answer. For details, see: https://dba.stackexchange.com/q/140381/1192 https://dba.stackexchange.com/q/84460/1192 Inline table-valued functions are expanded into the calling query text before optimization, so there is no special opportunity for early evaluation and caching. That does not mean the execution plan won't feature any cache-and-reuse optimizations. Where applicable, the optimizer will consider those (e.g. a spool) just as it would for a query without an inline function. Generally, the optimizer will be able to tell that SPLIT_STRING on a constant input returns deterministic results. The plan is likely to evaluate the split just once, but it does depend on plan shape. Assuming the splitter function is a multi-statement table-valued function, the result is very likely to be evaluated once. For details, and how you can tell if caching occurs from the execution plan, see: https://dba.stackexchange.com/q/159333/1192 All that said, you are right to be cautious about all this. The optimizer will frequently be able to evaluate expressions once and reuse the result, but this is not guaranteed, and often requires expert analysis to determine. Forcing materialization using temporary tables is frequently the most robust solution since it guarantees a particular order and number of evaluations.

You could change FOR r_row IN SELECT type FROM type_list to FOR r_row IN EXECUTE format( 'SELECT DISTINCT lower(key) AS type FROM %I', 'id_' || table_string ) I would suggest that you use list partitioning and lump all the keys that don't exist anywhere into a single partition.

The parentheses around the statement are wrong. It is explain plan for stmt and not explain plan for (stmt); https://dbfiddle.uk/?rdbms=oracle_21&fiddle=589ff1ba836d6c28add25ee532221f33

The data type timestamp with time zone (timestamptz) does not store any time zone information. That's a common misconception, inspired by the misleading name. (Blame the SQL standards committee!) You are not the first to fall for this: https://stackoverflow.com/a/28876266/939860 The time offset is just an input modifier / output decorator. Postgres always stores UTC time internally. Basics here: https://stackoverflow.com/a/9576170/939860 Consider this demo: test=> SET timezone = 'UTC'; SET test=> SELECT timestamptz '2022-01-01 18:53:11.14297-05'; timestamptz ------------------------------ 2022-01-01 23:53:11.14297+00 (1 row) test=> SET timezone = 'America/New_York'; SET test=> SELECT timestamptz '2022-01-01 18:53:11.14297-05'; timestamptz 2022-01-01 18:53:11.14297-05 test=> SELECT timestamptz '2022-03-21 18:53:11.14297-05'; timestamptz 2022-03-21 19:53:11.14297-04 (1 row) Note also how DST changes for the last call. See: https://dba.stackexchange.com/questions/160065/oddities-with-at-time-zone-and-utc-offsets/160083#160083 This also goes to show why your current function is inherently unreliable. A plain cast from timestamptz to date assumes the current time zone setting of your session. A date is not clearly defined without also giving the timezone it applies to. If you are fine with the fuzzy definition, consider this improved (but still naive) function: CREATE OR REPLACE FUNCTION laborable_day(INOUT _dt timestamptz) LANGUAGE plpgsql STRICT AS -- why SECURITY DEFINER ? $func$ BEGIN LOOP IF EXISTS ( SELECT FROM holidays WHERE holiday = _dt::date -- depends on current time zone! ) THEN _dt := _dt + interval '1 day'; ELSE EXIT; END IF; END LOOP; END $func$; I removed SECURITY DEFINER. Only use this when necessary, as it's potentially dangerous. Instead, grant SELECT for your holidays table to PUBLIC. Using an https://stackoverflow.com/a/8169928/939860 . About EXISTS: https://stackoverflow.com/a/11892796/939860 I also replaced your mixed-case identifiers. See: https://stackoverflow.com/a/20880247/939860 Deterministic function To get deterministic results, also define the time zone. Like CREATE OR REPLACE FUNCTION laborable_day(INOUT _dt timestamptz, _tz text DEFAULT 'UTC') -- default UTC? LANGUAGE plpgsql STRICT AS -- why SECURITY DEFINER ? $func$ DECLARE _day date := (_dt AT TIME ZONE _tz)::date; _inc int := 0; BEGIN LOOP IF EXISTS ( SELECT FROM holidays WHERE holiday = _day + _inc ) THEN _inc := _inc + 1; ELSE EXIT; END IF; END LOOP; _dt := _dt + interval '1 day' * _inc; END $func$; I added 'UTC' as DEFAULT for the second parameter. Adapt to your needs. About parameter defaults: https://stackoverflow.com/a/11604187/939860 You can just https://stackoverflow.com/a/68742129/939860 . I'd suggest not to "overload" the function (create variants with different function parameters), that can get tricky. This way you can still call the function giving just a timestamptz: SELECT laborable_day('2022-01-01 18:53:11.14297-05'); To get results for a given timezone, i.e. 'Europe/Vienna': SELECT laborable_day('2022-01-01 18:53:11.14297-05', 'Europe/Vienna'); Use time zone names, not abbreviations or numerical offsets, to make it work properly with DST and other oddities. https://dba.stackexchange.com/questions/160065/oddities-with-at-time-zone-and-utc-offsets/160083#160083 Find available time zone names in https://www.postgresql.org/docs/current/view-pg-timezone-names.html .

mysql> SELECT CONCAT(14.3); +--------------+ | CONCAT(14.3) | +--------------+ | 14.3 | +--------------+ Some notes: Since it is left justified, it is probably a string. CONCAT() generates a string. "14.3" and 14.3 can act as either a string or a number: mysql> SELECT CONCAT(14.3) + 0, "14.3" + 0; +------------------+------------+ | CONCAT(14.3) + 0 | "14.3" + 0 | +------------------+------------+ | 14.3 | 14.3 | +------------------+------------+ mysql> SELECT CONCAT(14.3) + 0, "14.3"; +------------------+------+ | CONCAT(14.3) + 0 | 14.3 | +------------------+------+ | 14.3 | 14.3 | +------------------+------+ mysql> SELECT 14.3 AS a_number, "14.3" AS a_string; +----------+----------+ | a_number | a_string | +----------+----------+ | 14.3 | 14.3 | +----------+----------+ Since things work either way, why do you care about the type? (I may need to dig deeper after you answer that.) I don't believe there is an introspective function for datatype.

You have to make it a valid SQL statement: SELECT badfunc('(VALUES ('done')) AS x(fullname); drop table tb_students; --', 1001);

I got triggered by the noise in your code. Consider the rewrite. While being at it, I also answered your question. CREATE OR REPLACE PROCEDURE sap_data.sp_ssa_po_final() LANGUAGE plpgsql AS $proc$ DECLARE mon text[]; sql_string text; BEGIN -- prepare array with subscripts -6 to +5, and current month at index 0 SELECT INTO mon ('[-6:5]={' || string_agg(to_char(m, 'mon'), ',') || '}')::text[] FROM generate_series(LOCALTIMESTAMP - interval '6 mon' , LOCALTIMESTAMP + interval '5 mon' , interval '1 mon') m; sql_string := 'CREATE TABLE public.sap_ssa_po_final AS SELECT' || concat_ws(E'\n , ' , E'\n mat_no AS material' , 'material_descr AS material_description' , '(' || concat_ws('+', mon[0], mon[-1], mon[-2], mon[-3], mon[-4], mon[-5], mon[-6]) || ') AS open_till_' || mon[0] , mon[1] || ' AS sum_' || mon[1] || 'qty' , mon[2] || ' AS sum' || mon[2] || 'qty' , '(' || concat_ws('+', mon[0], mon[-1], mon[-2], mon[-3], mon[-4], mon[-5], mon[-6], mon[1], mon[2]) || ') AS total_open' , mon[3] || ' AS sum' || mon[3] || 'qty' , mon[4] || ' AS sum' || mon[4] || 'qty' , mon[5] || ' AS sum' || mon[5] || '_qty' , 'now() AS created_date' ) || E'\nFROM v_ssa_po_summarised;' -- RAISE NOTICE '%', mon; -- RAISE NOTICE '%', sql_string; EXECUTE sql_string; -- These can be static. Spell out the schema to be sure! ALTER TABLE public.sap_ssa_po_final ADD PRIMARY KEY(material); CREATE INDEX ON public.sap_ssa_po_final (total_open); END $proc$; Primary key and index can just be created with static code, after the new table has been created. PL/pgSQL plans and executes one statement after the other. (The same would not be possible in a plain SQL function, where the whole function body is parsed at once. But dynamic SQL requires a procedural language anyway.) See: https://stackoverflow.com/a/24771561/939860 But I would advise to spell out the schema name to avoid mishaps (or even malicious attempts) with the search_path. I used public. (Your dynamic CREATE TABLE statement had no schema, yet.) Adapt to your case. Maybe the temporary schema pg_temp is an option for you? See: https://stackoverflow.com/a/9067777/939860 I generate the array of months mon with the current month at subscript 0. This allows to simplify quite a bit. About non-standard array subscripts: https://stackoverflow.com/q/12011569/939860 Note the strategic use of https://www.postgresql.org/docs/current/functions-string.html . See: https://stackoverflow.com/a/12320369/939860 I work with LOCALTIMESTAMP instead of now() to make clear that the local time zone will be used either way - which makes a difference for corner cases. It's also slightly more efficient with generate_series(). See: https://stackoverflow.com/a/46499873/939860

The errors results from a misunderstanding of the use of the anyelement type. Read in https://www.postgresql.org/docs/current/extend-type-system.html#EXTEND-TYPES-POLYMORPHIC : Each position (either argument or return value) declared as anyelement is allowed to have any specific actual data type, but in any given call they must all be the same actual type. So, you should use the function in the form https://stackoverflow.com/a/40300936/1995738 : create or replace function nvl (anyelement, anyelement) returns anyelement language sql as $$ select coalesce($1, $2) $$; and make sure that the actual type of arguments match exactly. If the column balance is numeric, the second argument also has to be numeric: select nvl(sum(balance), 0.0) -- or select nvl(sum(balance), 0::numeric) Update. The OP says: I can not change the SQLs. only adjust the function. In this case you cannot use anyelement arguments. You need to create function with numeric arguments: drop function if exists nvl (anyelement, anyelement); create or replace function nvl (numeric, numeric) returns numeric language sql as $$ select coalesce($1, $2) $$; The drawback is that the function will work only for numeric and/or integer arguments. Postgres allows to overload functions, so you can additionally create functions for other types, e.g.: create or replace function nvl (text, text) returns text language sql as $$ select coalesce($1, $2) $$;

You can't have a 20GB bytea value, no matter how much RAM you have. The max is 1GB. And you can't btree index values anywhere near that large--but it is not clear why you specify btree as you also talk about making new index types. What you could do is use a well-known hashing function (like md5 or sha256) to digest the full value on the client, and then truncate that to a length where you would have plausibility for collisions, then send the truncated value to the server to search with. The server could then send back all the matching full-length digests. (Or it could send back the full length documents, but not if they are >1GB). That way you would learn if your document (or its hash digest anyway) was already in the database, but without the database knowing with certainty which of those (if any) was the one you wanted to search for. A regular btree index would be sufficient for the indexing part of this.

If a function is declared IMMUTABLE, but the body contains functions of lesser volatility (STABLE, VOLATILE), then https://wiki.postgresql.org/wiki/Inlining_of_SQL_functions of SQL functions is disabled. So, yes, Postgres does care "what's inside". There are differences between SQL functions and functions with other procedural languages like PL/pgSQL. Dynamic SQL is not possible in SQL functions to begin with. Related: https://stackoverflow.com/a/24771561/939860 But for many purposes, Postgres is satisfied with the declared function volatility. For example, if you build an index involving a custom function, it must be declared IMMUTABLE. It is your responsibility to keep that promise, or the index will break silently. Example: https://stackoverflow.com/a/11007216/939860 https://www.postgresql.org/docs/current/xfunc-sql.html#XFUNC-SQL-POLYMORPHIC-FUNCTIONS (using anyelement et al.) is an orthogonal concept. Polymorphic functions can have any volatility declaration.

I re-wrote your proc to below: CREATE OR ALTER PROCEDURE [dbo].[sp_isSimpleRecovery] AS BEGIN PRINT DB_NAME() + CHAR(13); PRINT CAST(DATABASEPROPERTYEX(DB_NAME(), 'Recovery') AS varchar(20)) IF CAST(DATABASEPROPERTYEX(DB_NAME(), 'Recovery') AS varchar(20)) = 'SIMPLE' PRINT '--Database in SIMPLE recovery model' + CHAR(13); IF CAST(DATABASEPROPERTYEX(DB_NAME(), 'Recovery') AS varchar(20)) = 'FULL' PRINT '--Database in FULL recovery model' + CHAR(13); END GO Note that I'm ignoring bulk-logged recovery model. This procedure does work for me, on SQL Server 2019. I.e., if I'm in a database that is in simple mode, it returns SIMPLE and if I'm in a database which is in full mode it returns FULL. If above doesn't work for you on 2016, then possibly there has been a behavioral change between 2016 and 2019. In some cases it isn't enough to just name the procedure sp_ and have it in the master database. You also have to mark it as a system procedure using the undocumented sp_MS_marksystemobject. Note that this is undocumented, so use at own risk! The difference between the two is that without sp_MS_marksystemobject, the procedure is found if you are in for example Adventureworks, but is executed in the master database context. If you for instance select from sys.tables, you will see the tables in the master database. With sp_MS_marksystemobject, you tell SQL Server to be in the context of the current database when it executes the code in your procedure. I.e., system tables, system functions and such will now be resolved to use user database instead of the master database. So why the behavioural change between 2016 and 2019? We can't say, since we are in undocumented territory. The simple fact is that there is no supported way to create a "global" system stored procedure that resolves to the database that you are in.

While there isn't a feature of Functional Indexes in SQL Server per se, one can mimic a similar implementation via https://docs.microsoft.com/en-us/sql/relational-databases/indexes/indexes-on-computed-columns?view=sql-server-ver15 . Computed columns allow functions to be utilized. They also can even be persisted such that the result of a function is stored in the column on disk (as opposed to being evaluated every time at query runtime). So long as the requirements of the document I linked above are met, it's possible to index on top of a function result similar to Functional Indexes from PostgreSQL. One of the most important requirements is that the function is a https://docs.microsoft.com/en-us/sql/relational-databases/user-defined-functions/deterministic-and-nondeterministic-functions?view=sql-server-ver15 , meaning it outputs the same result for the same input always. For example, ISNULL() is deterministic because the same input parameter will always return the same outputted result. But the GETDATE() function is nondeterministic because the same input (which happens to be no input for this particular function) always results in a different outputted result, one day to the next, when it's called.

Only those records which contain valid https://developer.mozilla.org/en-US/docs/Web/Guide/HTML/XHTML#:%7E:text=XHTML%20is%20a%20term%20that,they%20should%20be%20served%20with will successfully https://docs.microsoft.com/en-us/sql/t-sql/functions/try-cast-transact-sql to XML as documented. Returns a value cast to the specified data type if the cast succeeds; otherwise, returns null. This appears to be the case for records 6571, 6569, and 6570 in your example. Note that once you've cast the record to XML, your XPath query still needs to reference a valid path within the XML document. The example path you've given is not valid for any of the example documents you've supplied. A valid path to return the data transform you want from records 6571, 6569, and 6570 is .value('(/p/span/text())[1]','nvarchar(1000)'). See https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=39a1028973f49a9cded541b1d1b92a0a Note the difference - you need to probe into "span" as well for these XML documents. This may not be the case for every document you want to probe - even those that are cast-able to XML. If you need help spot-checking what is or is not valid XML from your HTML sample data, you can try an https://www.freeformatter.com/xml-formatter.html if your sample data is safe to be made public. As you can see below, it will either colorize and add whitespace to the XML to make the valid paths more apparent to a human eye... ...or give you a verbose reason why the text you've supplied cannot be converted. Your success or failure in parsing will be case-by-case unless you get a better understanding of your source data. Data stored as XML (and JSON for that matter) documents can be arbitrary in a way that integers or dates are not. That can be useful or (in your case) annoying.

What went wrong That feature didn't exist in 11.2 You used the question SQL, not the answer SQL Correction My answer is the same. Define it in the outer most WITH statement. with function calculator (m number, r number) return number is begin return m * r; end calculator; select calculator(3, 2) as calc from dual

It is possible to define the class and add the method __call__Then a copy of this class can be called as a function, and access to its variables will also be provided.class First(object): def __init__(self): self.a = '1' self.b = '2' self.c = '3' def __call__(self): print(self.a, self.b, self.c) first = First() first() # Выведет 1, 2, 3 def second(): lst = [] lst.append(first.a) lst.append(first.b) lst.append(first.c) return lst print(second()) # Выведет ['1', '2', '3']

If the time is to be the same between the addition/deleting of the class, a flag may simply be created to indicate, add or delete the class.var scaleElem = document.getElementById('par_mak'); var addClass = true; setInterval(addClassToScaleElem, 1000); function addClassToScaleElem( ) { var classList = scaleElem.classList; if (addClass) { classList.add('infi_anim'); } else { classList.remove('infi_anim'); } addClass = !addClass; }#par_mak { width: 30px; height: 30px; background: blue; transition: all .3s linear; } .infi_anim { background: black !important; }<div id="par_mak"></div>If there's a difference, you can clean it again in the same - clearInterval(interval) and start again... interval = setInterval(addClassToScaleElem, counter);

Undefined properties Proxy::getHomeProduct♪ Check this properties, whether or not it is static.