New answers tagged

1

This query will give you the argument types and result types as arrays for any function: SELECT f.oid::regproc AS function_name, f.proargtypes::regtype[] AS argument_types, CASE WHEN f.proallargtypes IS NULL THEN ARRAY[f.prorettype::regtype] ELSE array_agg(args.type::regtype) FILTER (WHERE args.mode = 'o') END AS ...


0

There is built-in type called SYS.ODCINUMBERLIST which is a VARRAY(32767) of NUMBER, and EXECUTE privilege is granted on it to everyone (PUBLIC). You can use that for up to 32767 numbers, example: with function f1 (p_numbers SYS.ODCINUMBERLIST) return number as begin return p_numbers.count; end; select f1(SYS.ODCINUMBERLIST(1,2,3,4,5,6,7)) from dual; / F1(...


1

how to set the value of @MaxItemSeqNum to the returned result Either SET @MaxItemSeqNum = ( SELECT MAX([Table1].[Item_Seq_number]) FROM Table1 WHERE Batch_Id = 1 Group By Batch_Id ); or SELECT @MaxItemSeqNum = MAX([Table1].[Item_Seq_number]) FROM Table1 ...


2

A function may not see the same state of the database depending on whether it's VOLATILE or not, when this state is changing during the execution of the SQL statement it's called from, either because of the statement itself or because of another transaction if the isolation level is Read Committed. This is documented in https://www.postgresql.org/docs/...


2

Two big problems with using functions in a query are that cardinalities are not accurate and that the function code is called once per row. When setting a variable neither of these are a concern. So I would think, generally speaking, there would be no measurable performance impact.


3

Initially, it seems like this was a bug related to the new Scalar UDF Inlining feature added in SQL Server 2019, since you mentioned that disabling inlining resolved the problem. On further inspection, the function cannot be inlined due to the presence of a CTE in the function definition. Here's my (failed) attempt to reproduce the issue: USE [master]; GO ...


2

Based on a quick and simple test: ALL_ARGUMENTS select * from all_arguments where position = 0 and data_level = 0 and argument_name is null and data_type = 'REF CURSOR'; position 0 is the first argument of a program unit. If it does not have a name/level (data_level = 0 and argument_name is null), then it is the return argument of the function, and you ...


Top 50 recent answers are included