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

1
vote
2answers
222 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 $$ ...
0
votes
0answers
26 views

SQL Server Function For Calculating Average Excluding Outliers

I'm looking for a function that will let me take an average and exclude outliers. Basically something where: 1, 2, 1, 2, 7 Would average out to 1.5. I'm going to implement this in a financial ...
0
votes
1answer
23 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 . ...
0
votes
1answer
40 views

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

ok, So 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 When I run this I ...
1
vote
1answer
100 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
33 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
2answers
113 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 ...
0
votes
1answer
37 views

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

Friends, I have this Function: create function [dbo].[fn_xxx] ( @Nome varchar(100), @Descricao varchar(500) ) RETURNS @tbDados table ( Qtd int ) as Begin ...
-1
votes
0answers
17 views

DB2 - Sql to get only the multiple records starting from 1 in row number function

Please help me in writing a sql script to get only the multiple records in row number function inclusive of rownumber 1 record. Example: ***Column 1 Column2 Row_number*** Apple orange 1 ...
0
votes
1answer
30 views

Function to handles firstname and lastname to a specific format

I'm trying to figuring out a way to show a name properly. Let's say I have 2 variable : @Firstname = 'Marc-Oliver' @Lastname = 'Saint-Onge' I would like to have name shown as; M.-O. St.Onge Or ...
0
votes
2answers
55 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 ...
0
votes
0answers
338 views

Informix error 674: Routine (gc_init) can not be resolved.

I have created function in Informix like this. Now I want to drop that function. I have successfully dropped auxiliary functions e.g. DROP FUNCTION gc_init(VARCHAR(255)) When I try to drop ...
0
votes
1answer
40 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 ...
19
votes
1answer
12k views

Create index if it does not exist

I am working on a function that allows me to add an index if it does not exist. I am running into the problem that I cannot get a list of indexes to compare to. Any thoughts? This is a similar issue ...
0
votes
1answer
45 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
73 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] ...
-1
votes
1answer
27 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
34 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])) ...
0
votes
1answer
26 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 ...
2
votes
1answer
39 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
0answers
24 views

Using Code to Access Database Dump via pgAdmin

I looked over this question on how to import a data dump using pgadmin and found the following code: CREATE OR REPLACE FUNCTION db_to_csv(path TEXT) RETURNS void AS $$ tables RECORD; gmdeclare ...
0
votes
1answer
34 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
41 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
938 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
2answers
202 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
67 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
3answers
3k views

How do I create a user-defined aggregate function?

I need an aggregate function that MySQL doesn't provide. I would like it to be in MySQL's flavor of SQL (that is, not in C). How do I do this? What I'm stuck on is creating an aggregate function -- ...
4
votes
1answer
12k views

Invalid use of a side-effecting operator 'INSERT' within a function - Multiple Inserts in Function

If this is not the right forum please let me know and move it for me. I've just got a bunch of inserts like this toward the end of my scalar function: INSERT INTO [Raptor].[dbo].[UserRole] ...
0
votes
1answer
66 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 | ...
4
votes
1answer
517 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 ...
0
votes
0answers
34 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 ...
1
vote
2answers
170 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;
1
vote
1answer
156 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
0answers
26 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 ...
0
votes
1answer
126 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 ...
2
votes
2answers
98 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', ...
4
votes
2answers
2k views

Postgresql function to create table

I want to create a function in order to create a table with a specific structure pasing part of the name of the table as an argument so the name of the table is t_ . Similar to this: CREATE OR ...
4
votes
1answer
3k views

Transform all columns records to lowercase

I'm using PostgreSQL 9.1 and I have a users table with a login column. login names are case-sensitive, for example Bob, MikE, john. I would like to transform all these records into lowercase. ...
0
votes
2answers
119 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, ...
5
votes
2answers
689 views

SQL Server SMO and PowerShell formatting

I have probably been looking at this for too long to figure this out... What is the easiest way I could get this exported to a CSV format? Would it be better to dump this to a table in a database ...
0
votes
0answers
68 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
91 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 ...
2
votes
3answers
5k views

What's the difference between functions and stored procedures? [closed]

I heard that the difference between functions and stored procedures in SQL Server is that functions are not stored in the database while stored procedures are. Is that true? If not, what are the ...
2
votes
1answer
145 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 ...
0
votes
1answer
37 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
95 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 ...
4
votes
0answers
88 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
121 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 ...
1
vote
1answer
153 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 ...
0
votes
0answers
59 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 ...