New answers tagged

2

The deciding factor for Postgres is that the function concat() is defined stable and not immutable in the system catalog pg_proc: SELECT proname, provolatile, proargtypes, proargtypes[0]::regtype AS argtype, prosrc FROM pg_proc WHERE proname = 'concat'; proname | provolatile | proargtypes| argtype | prosrc --------+-------------+------------+---------+-----...


1

To fill out dezso's answer, here is an example of the same input yielding different outputs depending on database state: select concat(1.0000000000003::float8,56); 1.000000000000356 set extra_float_digits TO 3; select concat(1.0000000000003::float8,56); 1.0000000000002999856


1

I believe concat, defined in the documentation as follows: FUNCTION RETURN TYPE DESCRIPTION ----------------------------------------------------------------------------------- concat(str "any" [, str "any" [, ...] ]) text Concatenate the text repre- sentations ...


2

At the very basic level (and this is for SQL Server only as each vendor handles security slightly differently), IF others were not creating their own database objects (Stored Procedures, Functions, etc) and relying solely upon your API, then this is handled inherently through "Ownership Chaining". Ownership Chaining allows for implied permissions on objects ...


2

AIUI, your wish is to shorten the RETURNS clause of the function. Not sure if you want to establish a dependency on the row type of the table at the same time, but that would make sense here, too. The form RETURNS SETOFrettype relies on the used type to be stored in the system catalogs. The manual: The return type can be a base, composite, or domain ...


1

First, I assume that the fact that you're using join_date with an underscore in your code while the type uses joindate is a typo. Otherwise, you'd get a compilation error. You want to calculate the difference between join_date and the current date. My guess is that you want to use months_between to calculate the number of months between the two dates and ...


2

Step 1: Get the SQL script when the function / procedure was created SHOW CREATE FUNCTION my_schema_1.my_function; We will get an result looks like CREATE DEFINER=`mysqladmin`@`%` FUNCTION `my_function` (i_paramter1 char(255), i_paramter2 int) RETURNS char(3) CHARSET utf8 COLLATE utf8_bin READS SQL DATA BEGIN DECLARE result_value char(3); ...


3

The other answer explains that this is a restriction but not the reason why. The requirement is not always true. Scalar UDFs can be EXEC-ed and still use implicit resolution (example) I imagine this is to avoid naming collisions. If functions were allowed to be referenced without schema then someone that created their own function that happened to be ...


0

OK, based on feedback from Erwin Brandstetter above, I came up with a VERY nice solution detailed below: CREATE TABLE device_data ( collected TIMESTAMPTZ, id VARCHAR(100), data JSON, PRIMARY KEY(id,collected) ); COPY device_data (collected, id, data) FROM stdin; 2016-01-02 00:02:12+00 switch1.mycompany.com {} 2016-01-02 00:02:12+00 switch2....


10

Then why we can call function without prefix (schema), created under dbo? From Books Online Document on UDF's Scalar-valued functions can be invoked where scalar expressions are used. This includes computed columns and CHECK constraint definitions. Scalar-valued functions can also be executed by using the EXECUTE statement. Scalar-valued functions ...



Top 50 recent answers are included