A function performs some specified work, usually taking parameters as input. In terms of databases these may be specific to SQL or to the database vendor.

learn more… | top users | synonyms (2)

0
votes
0answers
17 views

Fire trigger when Routine was created in MySQL

I try to track changes of Stored Procedures and UDFs in MySQL. That means, if a SP or UDF is created, I want to copy the information into another table. I tried to clone the information_schema....
1
vote
0answers
32 views

Populate ID Field by Converting Group Name Values

This is a bit tricky to explain, but here goes. We have a System Rights database that loads system users and system rights or access levels into two different tables. The database is already built ...
5
votes
4answers
196 views

Implicit conversion warning despite persisted computed column vs. performance hit from UDF in persisted computed column definition

I have a need for a computed column to resolve an implicit conversion problem. I have a column that is declared as a VARCHAR that should only ever store integers, but is occasionally populated in ...
0
votes
0answers
26 views

mysql: can not find a function being called

I am new to mysql and there is a function (or something similar) in existing functions in the db. While I compile using the function on my server if compiles fine, but does not compile in my local. It ...
2
votes
1answer
69 views

Create a function from another function

Is it possible to create a new function from another plpgsql function? Something like this: CREATE OR REPLACE FUNCTION func_test() RETURNS VOID AS $BODY$ BEGIN CREATE OR REPLACE FUNCTION func_test2(...
0
votes
0answers
48 views

MySQL performance affected by calling functions from inside procedures?

this is the very first question I post, so please bear with me ... When working on a project, I tried to get the LAST_INSERT_ID for a table, by invoking it from inside a procedure right after an ...
6
votes
2answers
387 views

User Defined Function (UDF) in master database

I have a function which is used in more than a dozen databases. I want this function to be accessible in all the databases by writing it only once, so I want to write the function in master database ...
0
votes
1answer
27 views

Postgresql 9.4: How to find out the stack level of nested function

Here are the two cases: Function fA fetches some rows and returns a table Function fB calls function fA and returns a table Within fA, how could I know whether it's been called from the program or ...
0
votes
1answer
45 views

How simplify/optimize a search of keywords in two tables at the same time?

I'm trying to model a function which will be used by a search engine (of cars for the example). The last version of PostgreSQL will be installed on a Windows server (I don't know server's ...
0
votes
0answers
171 views

A “best practice” Postgresql function template for ETL processes

I use SQL Server in my day job, and have a personal project to build a star schema data warehouse in Postgres 9.4 on Ubuntu 14.04 to extend my knowledge (it'll be approximately 300GB uncompressed). I'...
2
votes
2answers
91 views

Does any SQL engine have navigation support in CHECK clauses?

Imagine a simple database to track personal spending. The most basic form of the model should let you create any number of accounts and any number of transactions (specifying the spending amount, date ...
1
vote
1answer
30 views

MySQL CHAR_LENGTH() alternative in IBM DB2

I have a value in a cell that has a Unicode character: "Nit€sh" I want to get the length in SB2. The problem is, If I use the length function it's considering '€' as 2 and total is 7. I am trying ...
1
vote
2answers
175 views

Permissions needed for dbms_metadata.get_ddl in a function

Oracle Enterprise 11.2.0.3 I'm receiving: ORA-31603: object "string of type "string" not found in schema "string" when executing a function that calls dbms_metadata.get_ddl. I know that ...
23
votes
2answers
1k views

Why does LEN() function badly underestimate cardinality in SQL Server 2014?

I have a table with a string column and a predicate that checks for rows with a certain length. In SQL Server 2014, I am seeing an estimate of 1 row regardless of the length I am checking for. This is ...
0
votes
1answer
35 views

plpy.execute('…') statements not in logs

How can I get the SQL statements executed by plyp into the postgres log files? CREATE FUNCTION history_monitor() RETURNS trigger LANGUAGE plpythonu AS $_$ ... plpy.execute('...') $_$; ...
0
votes
1answer
35 views

Unused Postgres sequence values

Observation: I have a serial index on a table in a PostgreSQL instance, and I execute a function that inserts a new record into that table. The function fails (reason not important), and the ...
3
votes
1answer
103 views

Need for recursive lookup, stuck in query design

In PostgreSQL 9.4, here is my table: with r(pk, userid,partneruserid) as (values (1,1,2), (2,1,3), (3,1,6), (4,2,5), (5,2,6), (6,3,1), (7,4,1), (8,5,3), (9,6,2), (10,6,3) ) select * from r; In a ...
2
votes
2answers
107 views

Union tables by mask in PostgreSQL [closed]

In database I have many tables like with names: "t_2015_09_01_z15" "t_2015_09_02_z15" "t_2015_09_03_z15" "t_2015_09_04_z15" "t_2015_09_05_z15" ... "t_2015_10_01_z15" "t_2015_10_02_z15" "...
0
votes
1answer
59 views

When to use stored procedure / UDF? [closed]

I'm confused about usage of SP / UDF. Typically, it is also possible to write code in programs outside of database. So is there any general advice to decide when to use them?
0
votes
0answers
34 views

Are MySQL UDF ACID compliant?

I had a Client Requirement wherein i had to write a MySQL UDF to generate AlphaNumeric Values. The input parameters for the Function where 1.Prefix Character 2.The Length of the AlphaNumeric Value 3. ...
0
votes
0answers
72 views

Unable to update table in MySQL Function

I have a requirement, where in i have create a function which will generate Alphanumeric Values based on the two Inputs..1. A string 2. A Digit. Like If the string is Mem..then Mem001,Mem002 and If ...
1
vote
1answer
43 views

Report Configuration changes

I created a stored procedure that I got on Erin Stellato's course on Pluralsight to report if there are any configuration changes on the server as a nightly job as follows: CREATEPROCEDURE dbo....
13
votes
2answers
283 views

How to implement a Set based algorithm/UDF

I have an algorithm that I need to run against every row in a table with 800K rows and 38 columns. The algorithm is implemented in VBA and does a bunch of math using values from some columns to ...
0
votes
1answer
79 views

Want to remove global_name from DB links (Oracle 11g)

I am trying to call remote function via DB LINKS. However, My global name contains the string ON and I believe it causes an error. The Error I have is ... SQL> var tmp varchar2(2); SQL> ...
1
vote
1answer
93 views

How to run ALTER TABLE on a list of tables sequentially in a function

I have a schema (called import) where data are imported as tables, and a function that processes them all. After that I want to move these tables away in another schema (called data_archive). The ...
0
votes
1answer
30 views

Cannot find either column “dba” or the user-defined function or aggregate “schema.functionName”, or the name is ambiguous

I created a function and granted select on [dba].[ufn_track_dailies] to public. The function shows up under table valued functions, but when I try to use it I get an error: select [dba].[...
1
vote
1answer
39 views

Store Procedure for adding cascade

I have a DB with 180 tables, and I was doing a logical deletion, now I need to do some testing with physical deletion. Is there a way to alter all my tables and to drop their fk's and add then add ...
3
votes
3answers
175 views

PostgreSQL trigger function that selects from %current% table

I have multiple tables that have the same column names, they only vary in their column values, like: tbl_log_a tbl_log_b tbl_log_c ... 26 tables from a to z. Each table has a trigger that calls a ...
1
vote
1answer
89 views

SQL Server deprecated Functions effect

I'm using SQL Server in various versions. In case of this I'm using functions which got deprecated. My question is, how a deprecated function affects the usage of SQL Server? The functions are ...
7
votes
2answers
6k views

Why scalar valued functions need execute permission rather than select?

I am wondering why, for scalar valued function, that I have to grant the user to execute rather than just a select? meanwhile a table valued functions works just fine with only select permission or ...
4
votes
1answer
163 views

Performance problems with TOP and user defined functions in views

I have problem with performance of queries on simple query on view. To keep things simple, assume my view is defined as follows: CREATE VIEW [mon].[ViewDevicesWithGroups] AS SELECT Id, Name, ...
3
votes
2answers
861 views

Password generator function

What's the best way of creating a password generator user defined function function in SQL Server if it is not possible to use non deterministic funtions within functions? I would like to create a ...
1
vote
1answer
92 views

Strange behaviour in TSQL function (parameter with int variable or NULL behaves differently)?

I experienced very strange problem today with TSQL function. The function has several parameters (int and bit). One of the (int) is set with value NULL. When calling a function with NULL inside the ...
1
vote
0answers
46 views

Speeding Up Query - Hangs at Sending Data - Indexed Correctly

I am looking for ways to speed up the below query. I currently have indices on FIRST, LAST, UNIT_PARENT, HomeAddressLine1, HomeAddressLine2, HomeCityNAme, HomeStateAbbreviation, HomeZipCode, HomeZip4, ...
2
votes
0answers
132 views

How to use variables in a PL/pgSQL function with dynamic queries?

...and when must I use EXECUTE ... quote_ident(variable)? Taking an input to the function as text and storing part as a variable: wtype := split_part(message, ' ', 4); realval := split_part(message,...
0
votes
0answers
13 views

Are User Defined functions always allowed on cloud services?

The question is, what is the policy on user defined functions on cloud services like Amazon RDS or Azure? Is it possible to load any User Defined Function? If so, isn't it possible to load some ...
4
votes
1answer
1k views

RETURN TABLE is converted to RETURN SETOF int in Postgres function

In pgAdminIII I create a function that returns multiple rows in a single column named identifier using RETURN TABLE: CREATE OR REPLACE FUNCTION test( IN parm1 date, IN parm2 interval) ...
2
votes
0answers
47 views

Is there a way to determine if a postgres function depends on a user-defined type?

I am writing a Ruby script to handle functions and user-defined types in Postgres. I want to make sure, that before a function is created in the database, if it uses a user-defined type, than this ...
1
vote
1answer
71 views

How to update table1 field by using other table and function

I have two tables and one function: Table1 contains shop_code,batch_id shop_code| batch_id | regstriy_id 123 | 100 | 12 124 | 100 |13 125 | 100 |12 Table2 contains shop_code,shop_name ...
0
votes
1answer
49 views

Function for generate a huge and possible random relationship between tables

I have this two tables and N:M relationship between them: Let's said I am managed to create a lot of media and emails records but I need to create the relationship between them. I can do this ...
0
votes
1answer
49 views

Usage of ExecSQL in UDF?

Problem: is it possible to use sp_executesql or EXEC inside of a user defined function? Thanks!
2
votes
2answers
160 views

How can I query a remote server using a local function?

I am querying a remote server for which I only have read permissions. I set up a local server with a UDF (since I cannot create one on the remote) that I would like to utilize for the query. When I ...
2
votes
1answer
3k views

PostgreSQL function definition “syntax error at or near +” [closed]

Suppose a simple query: (SELECT MAX(timestamp) FROM events e WHERE e.id < some_id) + ((SELECT MIN(timestamp) FROM events e WHERE e.id > some_id) - (SELECT MAX(timestamp) FROM events e ...
0
votes
2answers
99 views

When doing an update on a set of records, how do i get previous updates to show on current row to update in UDF [closed]

SO i have a rowset of 10 UIDs. They are all dupes, lets say DUPEUID. I have a UDF that will take the UID, check it against the table to see if there is a dupe, and update the UID to DUPEUID01. The ...
0
votes
0answers
116 views

For Postgres, how would I define a custom function to generate primary keys?

In postgres, primary keys are typically generated using a SEQUENCE. I am hoping to generate a unique random 12 character string for use as a primary key. How would I do this in Postgres?
3
votes
1answer
1k views

'ERROR: text search dictionary “unaccent” does not exist' during CREATE INDEX?

I'm running PostgreSQL 9.3 on Mac OS X Yosemite. I try to create an unaccent lowercase trigram index. To achieve it I did this: mydb=# CREATE EXTENSION pg_trgm SCHEMA public VERSION "1.1"; ...
1
vote
1answer
501 views

SQL query to sum a column prior to date and show all entries after that date

I have a database function that sum a column prior to a date and after that date show all entries one by one. that function return the data ordered by a common field and apply the sum by date every ...
4
votes
1answer
686 views

Calculating Average Excluding Outliers

I'm looking for a SQL Server function that will let me take an average, excluding outliers. Basically something where: 1, 2, 1, 2, 7 Would average out to 1.5. I'm going to implement this in a ...
0
votes
1answer
82 views

No records in Mysql.func table even though there are stored functions in server

When i am creating a user defined function in one of my mysql server, it's not creating any row in mysql.func table . Even though i have multiple functions in my mysql server , this table is empty . ...
2
votes
1answer
189 views

Joining two inline functions, slows considerably when filtering on second function

I have two inline functions that run quickly enough alone. When I left join them like this: select * from Function1(-1) F1 left join Function2(-1) F2 on F1.key = F2.key ...I get my result set in ...