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
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
125 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
517 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
738 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
203 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
263 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
30 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
58 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
190 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
301 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
129 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
47 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 ...
4
votes
1answer
10k 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
93 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
38 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
748 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 ...
2
votes
1answer
1k views

How to return a record from function, executed by INSERT/UPDATE rule (trigger)?

I'm having some issue when I'm trying to join two tables in a single view (for migrating code we want to split one big record into two records). I'm using Postgres 9.4! Here is an SQL fiddle. The ...
0
votes
1answer
54 views

Allow public use of functions without giving them database control

I came up with a set of functions that I would like to be accessed from a variety of places. SQL seemed like the best place for this due to accessibility, but I receive this error when trying to use ...
0
votes
1answer
155 views

Syntax Error In Postgresql Function [closed]

Why am I getting: ERROR: syntax error at or near "'SELECT '" In: CREATE OR REPLACE FUNCTION GetListings (lon double precision, lat double precision, schemas name[]) RETURNS TABLE( schema ...
2
votes
1answer
6k views

How to join a table with a table valued function?

I have a user defined function: create function ut_FooFunc(@fooID bigint, @anotherParam tinyint) returns @tbl Table (Field1 int, Field2 varchar(100)) as begin -- blah blah end Now I want to join ...
5
votes
2answers
7k views

Is it possible to define a function within a stored procedure?

I have a stored procedure: create proc sp_MyProc(@calcType tinyint) as begin -- some stuff collating data into #MyTempTable if (@calcType = 1) -- sum select A, B, C, CalcField = sum(...
1
vote
1answer
219 views

Next value for Sequence Function

Why does this give me the same value, four 1? USE [TSQL2012] GO IF OBJECT_ID('dbo.sqlsequence', 'SO') IS NOT NULL DROP SEQUENCE dbo.sqlsequence; GO CREATE SEQUENCE [dbo].[SQLSequence] AS INT ...
4
votes
2answers
2k views

Add function parameter to SQL query WHERE clause

I have a web application in java and it uses a query. I don't want to write the query into Java, so I made a function: CREATE OR REPLACE FUNCTION testFunc(inputs text) RETURNS TABLE(...) AS $$ ...
1
vote
0answers
331 views

How Do I Get DB2 NoSQL User-Defined Functions to Work

I am using NoSQL in DB2 10.5 FP4. I am creating and connecting to the database with the 'db2admin' administrator user ID on my Windows machine. I also used the 'db2 attach to DB2_01 as db2admin' ...
5
votes
2answers
342 views

How do I create a user-defined aggregate function in MySQL (redux)?

OK, I'm probably going to get banned for asking the same question a second time, but I'm an incredible newbie here and I was told I don't have enough of a reputation to comment on the original ...
4
votes
1answer
887 views

EXPLAIN ANALYZE shows no details for queries inside a plpgsql function

I am using a PL/pgSQL function in PostgreSQL 9.3 with several complex queries inside: create function f1() returns integer as $$ declare event tablename%ROWTYPE; .... .... begin FOR event IN ...
1
vote
2answers
695 views

Function extract() from date returns double precision value

Why does the Postgres function extract() return double precision from date value? Only reason I could imagine is, there are some locales using calendars (those not Gregorian calendars and supported ...
2
votes
1answer
1k views

How Do I Grant Permissions to Replace a Function?

I am getting the following error message (when trying to enable NOSQL in DB2 10.5, although I don't think that's really the issue): Error code: -551 DB2 SQL Error: SQLCODE=-551, SQLSTATE=42501, ...
1
vote
1answer
260 views

Implement a Parent-Level Count in Binary Search Tree

Based on this question, have any way to implement a parent level count? Find highest level of a hierarchical field: with vs without CTEs Example table: +----+-----------+ | id | parent_id | +----+-...
0
votes
0answers
44 views

Performance decline function vs command window

I have a function that I first wrote as a serious of commands. In the SQL command window when executed, those steps take about 8 seconds to return the data set. When those same commands are turned ...
2
votes
1answer
3k views

Why “SET LOCAL statement_timeout” does not work as expected with PostgreSQL functions?

My understanding is that PostgreSQL functions are executed similar to a transaction. However, when I tried to "SET LOCAL statement_timeout" within a function, it did not work. Here's how it works ...
2
votes
1answer
77 views

PLSQL using cursors or functions to query repeated data

Is it better to declare a commonly used lookup query as a package function or as a function (either returning rows or values or returning a sysref cursor)? Whats the difference? What are the best ...
2
votes
2answers
344 views

Call UPSERT function multiple times to different inputs

I have a function: merge_vehicles(vid, cid, vname, reg_no, name, name_1st) Can i call it multiple times on inputs like (2335, 55, '246BDH', '246BDH', '811', 1), (2336, 55, '038THX', '038THX', '831'...
1
vote
2answers
679 views

Assign to NEW by key in a Postgres trigger

In the trigger body, how can I assign a value to NEW by it's field name? That's what I want to do: some_key = "some_column"; NEW[some_key] = 5;
0
votes
1answer
423 views

MySQL UDF - wrong ELF class: ELFCLASS32

I'm having a problem creating a MySQL udf on my server (I was able to do it locally on OS X). I am compiling it like this on the server: gcc $(mysql_config --cflags) -I /usr/include/mysql -I. -shared ...
0
votes
2answers
556 views

Returning true or false in a custom function

I have this mysql table schema CREATE TABLE `nxt_records` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `tel_number` VARCHAR(50) NULL DEFAULT NULL, `nxt_status` VARCHAR(50) NULL DEFAULT NULL, ...
0
votes
0answers
177 views

postgres: access to schema objects within a function

Here's my problem: I have Schema A, owned by User 1, that owns all objects. I've created schema B, owned by User 2. I have granted User 2 all privileges to the objects owned by User 1. When logged ...
0
votes
1answer
931 views

Explain doesn't show indexed column as key

Consider the following table structure CREATE TABLE `games` ( `game_id` bigint(20) NOT NULL AUTO_INCREMENT, `players_no` tinyint(4) DEFAULT NULL, `startedon` datetime NOT NULL DEFAULT '0000-00-00 ...
0
votes
1answer
67 views

column value is not increment after 10

I tried below 2 codes.... showing same error 1.SELECT columnname FROM tablename ORDER BY columnname DESC LIMIT 1 SELECT MAX(columnname ) AS max FROM tablename I am trying to increment value of ...
0
votes
0answers
369 views

Why would call to scalar function inside a Table Value Function be slower than outside the TVF?

I am writing a Table Value Function, calling the function takes 10x as long as directly running the code. I traced this to a call to a multi-line scalar function inside the TVF. The call to the ...
1
vote
1answer
38 views

Handling exceptions from inside a function

I need to add a trigger after insert or update on a table so that, where some row is updated and a certain condition is met, then some other table needs to be updated. However, that last update ...
2
votes
3answers
3k views

Can I use PHP function strtotime() in SQL queries?

I have this table, and I need to use a PHP function in the query. id time lastTime 1 08:02 2 08:04 3 08:30 I am using this query with the strtotime(), and it's not working: ...
2
votes
1answer
187 views

How to apply a regex on a column fetched from DB

I am using IBM DB2. So, I am retrieving a column named SSN from a table. I have to apply the following regex on that column: ([1-57-8][0-9]{2} |0([1-9][0-9] |[0-9][1-9]) |6([0-57-9][0-9] |[0-9][0-57-...
-1
votes
1answer
271 views

View vs TableValued Function vs SubQuery

I have a scenario where i need to join with 3 tables to get the value for one field for second field join with 4 tables for third field join with 3 tables all above joins are inner and if data ...
4
votes
0answers
191 views

Why is it @@SERVERNAME but not @@DBNAME? [closed]

I'm just curious: To get the server name, I use SELECT @@SERVERNAME but to get the current database name, I use SELECT DB_NAME() Technically, both seem to be scalar functions: @@SERVERNAME is ...
1
vote
1answer
123 views

What does exec(text) do?

I just found the following function in two RDS PostgreSQL 9.3 DBs: -- -- Name: exec(text); Type: FUNCTION; Schema: public; Owner: adam -- CREATE FUNCTION exec(text) RETURNS text LANGUAGE plpgsql ...