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
21 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 ...
1
vote
3answers
46 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 ...
3
votes
3answers
3k 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
1answer
169 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 ...
1
vote
1answer
57 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 ...
5
votes
2answers
107 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 ...
2
votes
1answer
131 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 ...
1
vote
0answers
26 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, ...
1
vote
0answers
35 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 := ...
10
votes
2answers
4k views

Is there a means to set the owner of all objects in a PostgreSQL database at the same time?

http://stackoverflow.com/questions/1348126/modify-owner-on-all-tables-simultaneously-in-postgresql describes some nifty ways to change table and other objects to a specific user, and it works ...
3
votes
1answer
123 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 ...
4
votes
1answer
3k views

Function/procedure to use dblink to fetch remote data and insert to multiple local tables

Am trying to fetch data from remote db and insert into three local tables. Currently I have a query which is successfully getting the data into one table. But now I was thinking of creating a ...
3
votes
1answer
67 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) ...
28
votes
2answers
17k 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 ...
1
vote
0answers
29 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 ...
3
votes
3answers
6k 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 ...
0
votes
2answers
81 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
41 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
21 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
25 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
0answers
21 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
2answers
1k views

Recursive update for tree structure in PostgreSQL

I have the following structure of table. id chNum parentid --- ------ --------- 1 1 NULL 2 1.1 1 3 1.1.1 2 4 2 ...
0
votes
0answers
31 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( ...
2
votes
1answer
657 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
0answers
54 views

MySQL: Hierarchical query to get the direct and indirect child

I am just trying to modify the code as in MySQL: Tree-Hierarchical query to get values inside single quotes and seems like the system hangs. Not sure where I am going wrong.. Here is the code. BEGIN ...
0
votes
0answers
52 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
293 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
165 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 ...
1
vote
2answers
819 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
1answer
48 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
62 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 ...
0
votes
1answer
78 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
266 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
311 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 ...
0
votes
1answer
32 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
1answer
88 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
71 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
116 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
37 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
37 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
31 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
61 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
30 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
41 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
63 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
2k 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
1k 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
129 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
13k 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] ...