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

0
votes
1answer
105 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
44 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 ...
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
78 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
36 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
1answer
49 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
97 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
2answers
4k 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 = ...
1
vote
1answer
171 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 ...
3
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 $$ ...
3
votes
1answer
385 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
432 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 ...
1
vote
1answer
197 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
42 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
67 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
240 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', ...
1
vote
2answers
456 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
318 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
440 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
128 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
616 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
51 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
297 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 ...
2
votes
1answer
151 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] ...
4
votes
0answers
147 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
122 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
100 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
425 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. ...
2
votes
1answer
344 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
3k 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
1k 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 ...
4
votes
1answer
3k views

Use NOLOCK hint when calling table valued function

Suppose the following function: CREATE FUNCTION [dbo].[ufnTest]() RETURNS TABLE AS RETURN SELECT 1 AS Nr My actual function will select actual data from many different tables. I (mostly) understand ...
0
votes
1answer
33 views

Mysql functions for web apps

I have few questions about using functions in a MySQL database for plugins and extensions of web apps like Magento or Wordpress. Mostly this is for a few extensions I want to do for Magento. The ...
1
vote
1answer
288 views

Postgres : Executing SELECT within a transaction does not return most recent rows

Given a table layout that looks like this inv_items ========= * id bigserial sku character varying(22) name character varying(32) ... inv_items_stock =============== item_id bigint (inv_items) qty ...
6
votes
5answers
1k views

Performance tuning a Great Circle distance calculation

I have a query that I am using to find locations that are within 1km of a known point. To do this, I am using the Spherical Law of Cosines formula with my latitude and longitudes. Currently, the ...
2
votes
1answer
169 views

How to create a function(table_name) that return a collection in Oracle?

I need to create a function in Oracle that accepts a table name and return a collection the content of which is based the table that accepted. I've been doing some search, many examples are of the ...
0
votes
1answer
77 views

Postgresql Function Trying to Insert into 1 row instead of 2

Postgresql 9.3, Mac OS X 10.6.7 I created this function and I would like to insert both results into the same row. Obviously the way I have it inserts into 2 rows. First, here is the Table T1: ...
1
vote
2answers
1k views

Function execute query, manipulates result and then return the same result

I need a function that does something like this pseudocode: function get_data() RETURN SET OF something... as BEGIN myResultSet = select id, some_other_column from ...... limit 20000; update ...
4
votes
3answers
6k views

how to create backup for stored procedure,trigger and functions of a database in sql server 2008?

I'm having 2 databases in 2 different servers(.246 and .252). I need to create backup for all stored procedures, triggers and function from 252 server and restore it in 246 server.database name in ...
0
votes
2answers
872 views

Remove a period if it is the first character in a varchar column

I am migrating data from an old system into a new system I am building and have found someone is doing a bad practice of adding a period to the start of some people's names to make them appear first ...
0
votes
1answer
118 views

Restricting Insert and Update to functions

We have a function in postgresql that inserts some rows in a table and updates some other rows based on an input provided by the user. Is it possible to somehow execute the function without giving ...
4
votes
1answer
2k views

Is there a First() aggregate function in postgresql that returns the exact same type as the input?

Full question re-write I'm looking a First() aggregate function. Here I found something that almost works: CREATE OR REPLACE FUNCTION public.first_agg ( anyelement, anyelement ) RETURNS anyelement ...
3
votes
1answer
179 views

How to create function(table_name) that returns a set of the table

I have a set of tables that share the same fields (some timestamps and users names used for version control). I need to create a function with dynamic sql that uses a table_name and a timestamp as ...
1
vote
2answers
289 views

Latitude and Longtitude Distance Calculation [closed]

I'm using MySQL and I am trying to calculate the distance in meters between two different longitude, latitude coordinates. I wrote a stored function in MySQL but it calculates just 1 for each row in ...
0
votes
1answer
956 views

how to correctly return values from a function

I would like to create a function that queries a user table by passing a username and a password, the function is supposed to return 'Y' if the username / password are correct and 'N' if they're not. ...
1
vote
0answers
1k views

How to use the result of regexp_split_to_array() in stored procedure [closed]

I'm trying to write a function used by an trigger BEFORE INSERT on a table; the function should convert a raw GPS string to useable columns in a different table. The point where I get stuck is the ...
6
votes
2answers
1k views

Is this temp table behaviour documented?

Query Language (SQL) and PL/pgSQL functions treat temp tables differently: begin; create table foo(id) as values (1); select * from foo; /* id ---- 1 */ savepoint s; create function f() returns ...
1
vote
2answers
5k views

Datediff() in MySQL

While calculating difference between 2 dates as number days, by using MySQL Datediff() function, I am facing some issue, the SQL code is given below. select datediff((select curdate()), (select ...