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)

2
votes
2answers
87 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
50 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
33 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
52 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 ...
13
votes
2answers
270 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
63 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
83 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
28 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 ...
1
vote
1answer
38 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
142 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
84 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
4k 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
156 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, ...
2
votes
1answer
620 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
85 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
45 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
127 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 := ...
0
votes
0answers
12 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
45 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
67 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
0answers
24 views

mysql Function not picking up parameter

I have written the following function: DECLARE thetotal INTEGER; select count(stock_current.EntryNo) INTO thetotal FROM stock_current WHERE (stock_current.PartNo = thepart) AND ...
0
votes
1answer
47 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
47 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
149 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 ...
0
votes
0answers
42 views

SQLAnywhere: How to apply function to list members?

I'm a newbie to SQL Anywhere... what a difference from the big O! I'm working in SQL Anywhere 12. I have a query that returns a column values that are a list of big int numbers, EXAMPLE: ...
0
votes
0answers
36 views

Pgsql Function to create multiple child tables inherited from parent tables

I need help with pgsql function to create multiple child tables concatenated with date. Inherited from parent tables. I am using the following queries -- Setting date range SELECT CAST( ...
0
votes
0answers
117 views

MySQL Queries in UDF

I would like to create a MySQL UDF (i.e. User Defined Function) that embeds a query; for instance, suppose the UDF is named foobar: mysql> SELECT foobar(); When foobar function receives the ...
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
97 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
108 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
910 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
442 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
588 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
76 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 . ...
1
vote
1answer
165 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 ...
2
votes
2answers
186 views

PostgreSQL - is it possible to use a plpgsql function in a bash script?

Target : What i'm trying to achieve is to restore a partial backup of a database (only certain rows that satisfied certain conditions have been backed up). Problem : Since between the partial-backup ...
1
vote
1answer
1k views

Error with FUNCTION ( SQL Server 2008 ) - ''Cannot find either column…'

I have this Function: CREATE FUNCTION [dbo].[fn_xxx] ( @Nome VARCHAR(100), @Descricao VARCHAR(500) ) RETURNS @tbDados TABLE ( Qtd INT ) AS BEGIN DECLARE @CMD AS ...
1
vote
0answers
26 views

CLR UDT SCH-M blocking

I’m hoping someone can provide some guidance on a problem I’m having with schema locking on C# CLR UDT’s. I’ve been on the Internet for an extended period of time researching my problem without ...
2
votes
1answer
54 views

Function to convert first names to a specific format

I'm trying to figure out a way to show a name properly. Let's say I have 2 variables: @Firstname = 'Marc-Oliver' @Lastname = 'Saint-Onge' I would like to have name shown as M.-O. St.Onge Or at ...
0
votes
2answers
148 views

AVG function returns wrong average

I have a simple table with a column of type REAL. There are 2 rows in the table and the values are 0.23 and 0.24. If I run the following: SELECT AVG(MyColumn) FROM dbo.MyTable I would expect to ...
1
vote
1answer
248 views

How to use MySQL stored function argument to refer to column name?

How can I use a function argument s to stand in for a column name in a query? I'm using mysql 5.5. I tried SELECT count_distinct(last_name) but it returns ERROR 1054 (42S22): Unknown column ...
0
votes
1answer
122 views

Convert an interaction matrix into a ternary relation

I have what I'm calling a sparse "interaction matrix" of data in MS Excel that I wish to convert into a "ternary relation" (table) in Access. The spreadsheet data are in this pattern: Name Act-1 ...
-2
votes
1answer
46 views

Postgresql - Stuck building a plpgsql function [closed]

I am a beginner in plpgsql and i am stuck coding a function. I need a function that does the following: Giving a table, a id_field in that table and another field in that table it does: ORIGINAL ...
3
votes
1answer
8k views

How to grant permissions on a table-valued function

Am I doing it right...? I have a function that returns money... CREATE FUNCTION functionName( @a_principal money, @a_from_date datetime, @a_to_date datetime, @a_rate float ) RETURNS money AS ...
0
votes
0answers
38 views

Wrapping code in function is causing a huge delay

I have the following function: CREATE FUNCTION [dbo].[FunctionScore] ( @Param1 ... ,@ParamN ) RETURNS TABLE RETURN ( SELECT T1.[Col001] ,SUM(ISNULL(F1.[Pts], T2.[Pts])) ...
2
votes
1answer
85 views

How to Create Aggregate function in SQL Server 2008

My requirement is to create concatenate function so I am trying to create Aggregate function as shown CREATE AGGREGATE [dbo].[Concatenate] (@value [NVARCHAR](MAX)) RETURNS[NVARCHAR](MAX) EXTERNAL ...
0
votes
1answer
37 views

Update statement with a function

I'm just curious. I was trying to optimize an update statement with a function Ex update circle set area = fnGetArea(radius) where ...<bunch of conditions> This update went 5-10mins and when ...
0
votes
2answers
661 views

Using output value of column from function for another column in SQL*Loader

I have asked the same question on StackOveflow. Do not know, if this site is linked to that or not and thus whether that question will be seen by professionals here, so I am adding it here. Please ...