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
239 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
29 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
183 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
291 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
126 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
9k 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
89 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
726 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
150 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 ...
4
votes
2answers
6k 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
209 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
323 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
336 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 ...
3
votes
1answer
798 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
649 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
975 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
255 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
43 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
2k 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
76 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
330 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
641 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
412 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
549 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
165 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
898 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
64 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
362 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
37 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
186 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
264 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
185 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 ...
0
votes
0answers
116 views

Index on fields with a function

I have a log table. It logs visitor data (time of visit, browser, window size ...). I want to create reports. I use a query like this (MySQL): SELECT COUNT(*) as raw_views ... FROM logs WHERE ...
-1
votes
4answers
525 views

Index on 'GROUP BY' with functions

I have a log table. It logs visitor data (time of visit, browser, window size ...). I want to create reports. I use a query like this (MySQL): SELECT COUNT(*) as raw_views ... FROM logs WHERE ...
0
votes
1answer
54 views

different outcome based on function execution method

I am mainlining a legacy system and realize that the answer to this may take a deeper explanation of the code but before I go that far I wanted to make sure the solution wasn't something more simple. ...
4
votes
1answer
561 views

Is support for Parallel Scalar UDF a reasonable feature request?

It is fairly well documented that scalar UDF's force an overall serial plan. Running functions in parallel Given a large number of rows coming into a point in the pipeline where a UDF must be ...
2
votes
1answer
389 views

Prevent postgres function of running more than 1 instance at the same time

DBMS: Postgres 9.3.4 OS: Debian 7 I have a plpgsql function that will be put in crontab to run every 15 minutes. The function should finish in about 8 minutes, but just in case it takes more than 15 ...
2
votes
1answer
4k views

How to pass a parameter into a function

At the moment I have a view created, see it here in the answer. How can I create a function based on that crosstab() query , so I can pass a date, and get data for the specific date? Also is it good ...
2
votes
3answers
2k views

How to log DML statements executed by a pl/pgsql function?

I have a pl/pgsql function (see below) that lists some fields and clears their contents using dynamically constructed UPDATE commands. When I set log_statement = 'mod', I can see nothing on the log ...